title: Converting xml values to rows in SQL Server like oracle XAMLTABLE
body: <p>I have a table with XML values in this format</p>
  <pre><code>id| xml_record 
  --+------------
  1 | xmlvalue
  2 | xmlvalue
  </code></pre>
  <p>The xml value is in this format</p>
  <pre><code>&lt;record&gt;
      &lt;c2&gt;date1&lt;/c2&gt;
      &lt;c2 m=&quot;2&quot;&gt;date2&lt;/c2&gt;
      &lt;c2 m=&quot;3&quot;&gt;date3&lt;/c2&gt;
      &lt;c3&gt;number1&lt;/c3&gt;
      &lt;c3 m=&quot;2&quot;&gt;number2&lt;/c3&gt;
      &lt;c3 m=&quot;3&quot;&gt;number3&lt;/c3&gt;
  &lt;/record&gt;
  </code></pre>
  <p>I need a query to generate this:</p>
  <pre><code>id | pmt_pos | pmt_date | pmt_number
  ---+---------+----------+-----------
  1  |  1      | date1    | number1
  1  |  2      | date2    | number2
  1  |  3      | date3    | number3
  </code></pre>
  <p>In Oracle I use the following query successfully to generate two virtual tables with the id's, dates and number and join them on the id and the position of every row:</p>
  <pre><code>SELECT t1.id
      ,t1.pmt_pos
      ,t1.pmt_date
      ,t2.pmt_number
  FROM (
      SELECT t.id
          ,xt.pmt_date
          ,nvl(xt.pmt_pos, 1) pmt_pos
      FROM myTable t
          ,XMLTABLE('/row/c2' passing t.xml_record columns pmt_date VARCHAR(8) path '/', pmt_pos VARCHAR(3) path '@m') xt
      ) t1
  JOIN (
      SELECT t.id
          ,xt.pmt_number
          ,nvl(xt.pmt_pos, 1) pmt_pos
      FROM myTable t
          ,XMLTABLE('/row/c34' passing t.xml_record columns pmt_number VARCHAR(50) path '/', pmt_pos VARCHAR(3) path '@m') xt
      ) t2 ON t1.id = t2.id
      AND t1.pmt_pos = t2.pmt_pos
  ORDER BY t1.id
  </code></pre>
  <p>Is there an equivalent function to Oracle xmltable in SQL or another way to achieve the same results?</p>