title: Transform SQL table to XML with column as parent node
body: <p>I'm trying to transform a table to an XML struture and I want one of the columns in my table to represent a parent node and the other column to represent a child node.</p>
  
  <p>I have got part of the way but I don't have the complete solution. I need the TABLE_NAME column to transform to a xml parent node and the COLUMN_NAME column to transform as child nodes. If I execute the following I get the nesting but I also get multiple parent nodes.</p>
  
  <pre><code>select
   TABLE_NAME AS 'tn',
   COLUMN_NAME AS 'tn/cn'
  from (
   select 'TABLE_A' AS TABLE_NAME, 'COLUMN_1' AS COLUMN_NAME
   UNION ALL
   select 'TABLE_A' AS TABLE_NAME, 'COLUMN_2' AS COLUMN_NAME
   UNION ALL
   select 'TABLE_B' AS TABLE_NAME, 'COLUMN_1' AS COLUMN_NAME
   UNION ALL
   select 'TABLE_B' AS TABLE_NAME, 'COLUMN_2' AS COLUMN_NAME
  ) x
  for xml path(''), ROOT('datatable')
  </code></pre>
  
  <p>OUPUT>>></p>
  
  <pre><code>&lt;datatable&gt;
    &lt;tn&gt;TABLE_A&lt;cn&gt;COLUMN_1&lt;/cn&gt;&lt;/tn&gt;
    &lt;tn&gt;TABLE_A&lt;cn&gt;COLUMN_2&lt;/cn&gt;&lt;/tn&gt;
    &lt;tn&gt;TABLE_B&lt;cn&gt;COLUMN_1&lt;/cn&gt;&lt;/tn&gt;
    &lt;tn&gt;TABLE_B&lt;cn&gt;COLUMN_2&lt;/cn&gt;&lt;/tn&gt;
  &lt;/datatable&gt;
  </code></pre>
  
  <p>DESIRED OUTPUT >>></p>
  
  <pre><code>&lt;datatable&gt;
    &lt;TABLE_A&gt;
     &lt;cn&gt;COLUMN_1&lt;/cn&gt;
     &lt;cn&gt;COLUMN_2&lt;/cn&gt;
    &lt;/TABLE_A&gt;
    &lt;TABLE_B&gt;
      &lt;cn&gt;COLUMN_1&lt;/cn&gt;
      &lt;cn&gt;COLUMN_2&lt;/cn&gt;
    &lt;/TABLE_B&gt;
  &lt;/datatable&gt;
  </code></pre>
  
  <p>Is this possible or am I dreaming? and is it possible without XML EXPLICIT or is this the kind of thing EXPLICIT is there for?</p>
  
  <p>The other possiblity I've been trying is to stuff the xml and then apply an xquery, but no joy with that yet.</p>
  
  <p>Thanks,</p>
  
  <p>Gary</p>