title: Cross Apply to get child parent value from Xml in SQL Server
body: <p>I have the following XML:</p>
  
  <pre><code>&lt;root&gt;
    &lt;row value="US"&gt;
      &lt;col value="00"&gt;Jon&lt;/col&gt;
      &lt;col value="01"&gt;David&lt;/col&gt;
      &lt;col value="02"&gt;Mike&lt;/col&gt;
      &lt;col value="03"&gt;Nil&lt;/col&gt;
    &lt;/row&gt;
    &lt;row value="Canada"&gt;
      &lt;col value="C1"&gt;Pollard&lt;/col&gt;
    &lt;/row&gt;
    &lt;row value="Japan"&gt;
      &lt;col value="J1"&gt;Yin&lt;/col&gt;
      &lt;col value="J2"&gt;Li&lt;/col&gt;
    &lt;/row&gt;
    &lt;row value="India"&gt;
      &lt;col value="MP"&gt;Ram&lt;/col&gt;
      &lt;col value="UP"&gt;Paresh&lt;/col&gt;
      &lt;col value="AP"&gt;Mohan&lt;/col&gt;
    &lt;/row&gt;
  &lt;/root&gt;
  </code></pre>
  
  <p>and I want the following output by using SQL Server query:</p>
  
  <pre><code>US  00  Jon
  US  01  David
  US  02  Mike
  US  03  Nil
  Canada  C1  Pollard
  Japan   J1  Yin
  Japan   J2  Li
  India   MP  Ram
  India   UP  Paresh
  India   AP  Mohan
  </code></pre>
  
  <p>I am using following SQL query:</p>
  
  <pre><code>declare @x xml
  set @x =
  '&lt;root&gt;
    &lt;row value="US"&gt;
      &lt;col value="00"&gt;Jon&lt;/col&gt;
      &lt;col value="01"&gt;David&lt;/col&gt;
      &lt;col value="02"&gt;Mike&lt;/col&gt;
      &lt;col value="03"&gt;Nil&lt;/col&gt;
    &lt;/row&gt;
    &lt;row value="Canada"&gt;
      &lt;col value="C1"&gt;Pollard&lt;/col&gt;
    &lt;/row&gt;
    &lt;row value="Japan"&gt;
      &lt;col value="J1"&gt;Yin&lt;/col&gt;
      &lt;col value="J2"&gt;Li&lt;/col&gt;
    &lt;/row&gt;
    &lt;row value="India"&gt;
      &lt;col value="MP"&gt;Ram&lt;/col&gt;
      &lt;col value="UP"&gt;Paresh&lt;/col&gt;
      &lt;col value="AP"&gt;Mohan&lt;/col&gt;
    &lt;/row&gt;
  &lt;/root&gt;'
  
  select r.value('@value','varchar(100)'),r.value('.','varchar(100)')
  from @x.nodes('root') as m(c)
  cross apply m.c.nodes('row/col') as x(r)
  </code></pre>
  
  <p>I am unable to get first column which contains the value of parent row. 
  Can you please suggest what changes I can make to get first column value?</p>