title: Convert XML to table SQL Server 2005
body: <p>I wonder how I can read a XML data and transform it to a table in T-SQL?</p>
  
  <p>For example:</p>
  
  <pre><code>&lt;t1&gt;
        &lt;t2&gt;
          &lt;val&gt;Opel&lt;/val&gt;
          &lt;t3&gt;Merriva&lt;/t3&gt;
          &lt;t3&gt;Zafira&lt;/t3&gt;
        &lt;/t2&gt;
        &lt;t2&gt;
          &lt;val&gt;Fiat&lt;/val&gt;
          &lt;t3&gt;Albea&lt;/t3&gt;
        &lt;/t2&gt;
    &lt;/t1&gt;
  </code></pre>
  
  <p>To:</p>
  
  <p>Table1:</p>
  
  <pre><code>id      value
  ----------------
  1        Opel
  2        Fiat
  </code></pre>
  
  <p>Table2:</p>
  
  <pre><code>id      id_Table1       value
  -----------------------------------
  1          1            Merriva
  2          1            Zafira
  3          2            Albea
  </code></pre>