title: SQL Server - convert XML Column to delimited string
body: <p>Gurus,
  I have XML Column value like below in SQL 2012</p>
  
  <pre><code>'&lt;XMLDoc&gt;
  &lt;AAA&gt;
    &lt;Name&gt;Name_A&lt;/Name&gt;
    &lt;Value&gt;Val_A&lt;/Value&gt;
  &lt;/AAA&gt;
  &lt;AAA&gt;
    &lt;Name&gt;Name_B&lt;/Name&gt;
    &lt;Value&gt;Val_B&lt;/Value&gt;
  &lt;/AAA&gt;
  &lt;AAA&gt;
    &lt;Name&gt;Name_C&lt;/Name&gt;
    &lt;Value&gt;Val_C&lt;/Value&gt;
  &lt;/AAA&gt;
  &lt;AAA&gt;
    &lt;Name&gt;Name_D&lt;/Name&gt;
    &lt;Value&gt;Val_D&lt;/Value&gt;
  &lt;/AAA&gt;
  &lt;AAA&gt;
    &lt;Name&gt;Name_E&lt;/Name&gt;
    &lt;Value&gt;Val_E&lt;/Value&gt;
  &lt;/AAA&gt;
  :
  :
  &lt;AAA&gt;
    &lt;Name&gt;Name_Z&lt;/Name&gt;
    &lt;Value&gt;Val_Z&lt;/Value&gt;
  &lt;/AAA&gt;
  &lt;/XMLDoc&gt;'
  </code></pre>
  
  <p>We have requirement to store first 2 nodes in individual columns and the remaining nodes (length may be 0 to n) as delimited string in third column. </p>
  
  <p>I have tried SQL as below, need help to populate 3rd column</p>
  
  <pre><code>SELECT Col1 =  Col.value('(/AAA/Value)[1]', 'varchar(255)') ,
   Col2 =  Col.value('(/AAA/Value)[2]', 'varchar(255)') 
  FROM table 
  </code></pre>
  
  <p>We need Output as</p>
  
  <pre><code>Col1               Col2                    Col3
  Val_A              Val_B                   Val_c,Val_D,Val_E....Val_n
  </code></pre>