Transform SQL table to XML with column as parent node

Question

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.

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.

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')

OUPUT>>>

<datatable>
  <tn>TABLE_A<cn>COLUMN_1</cn></tn>
  <tn>TABLE_A<cn>COLUMN_2</cn></tn>
  <tn>TABLE_B<cn>COLUMN_1</cn></tn>
  <tn>TABLE_B<cn>COLUMN_2</cn></tn>
</datatable>

DESIRED OUTPUT >>>

<datatable>
  <TABLE_A>
   <cn>COLUMN_1</cn>
   <cn>COLUMN_2</cn>
  </TABLE_A>
  <TABLE_B>
    <cn>COLUMN_1</cn>
    <cn>COLUMN_2</cn>
  </TABLE_B>
</datatable>

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?

The other possiblity I've been trying is to stuff the xml and then apply an xquery, but no joy with that yet.

Thanks,

Gary

Answer 1

As others have mentioned, FOR XML doesn't allow you to dynamically name nodes. The node names have to be constants by the time the query itself is compiled. You can work around this with dynamic sql but then you end up with code that gets harder and harder to read.

An alternative would be to manually generate the talbe name nodes and CAST into XML:

Setup:

CREATE TABLE a (table_name VARCHAR(20), column_name VARCHAR(20)
INSERT INTO a VALUES ('TABLE_A', 'COLUMN_1')
INSERT INTO a VALUES ('TABLE_A', 'COLUMN_2')
INSERT INTO a VALUES ('TABLE_B', 'COLUMN_1')
INSERT INTO a VALUES ('TABLE_B', 'COLUMN_2')

Execute:

SELECT CAST(
      '<' + table_name + '>'
    + (SELECT c.column_name as 'CN'
         FROM a c
        WHERE c.table_name = p.table_name
       FOR XML PATH('')) 
    + '</' + table_name + '>'
    AS XML)
  FROM a p
GROUP BY p.table_name
FOR XML PATH(''), ROOT('datatable')

Produces:

<datatable>
  <TABLE_A>
    <CN>COLUMN_1</CN>
    <CN>COLUMN_2</CN>
  </TABLE_A>
  <TABLE_B>
    <CN>COLUMN_1</CN>
    <CN>COLUMN_2</CN>
  </TABLE_B>
</datatable>

Answer 2

Unfortunately - what you are trying to do is not possible. Two major issues (if you have leeway on either there might be a solution).

First is that NONE of the XML options in SQL (not even using EXPLICIT) allow for dynamic node naming. You can either use an attribute <tn id="TABLE_A" /> or a value <tn>TABLE_A</tn> but you can't get <TABLE_A> unless you hardcode it.

XML types do allow for nesting/subqueries.

SELECT V1.tbname
,(SELECT V2.colname FROM testtable V2 
  WHERE V1.tbname = V2.tbname FOR XML PATH(''), ELEMENTS, TYPE)
FROM testtable V1
FOR XML AUTO, ROOT('datatable')

Your second issue comes from the fact that your data is denormalized. There is no way to get a unique list of tables (you can't use DISTINCT in the above because SQL can't compare XML types). This limits what you can do in a single "pass" (statement).

If you are willing to use a temp table (or table variable) you can select a distinct list of table names and then join that with column names as in the example given (run the following first and replace the outer from with @tblist).

DECLARE @tblist TABLE (tbname varchar(20))
INSERT INTO @tblist SELECT DISTINCT tbname FROM testtable  

It returns this:

<datatable>
  <V1 tbname="TBA">
    <colname>COL 1</colname>
    <colname>COL 2</colname>
  </V1>
  <V1 tbname="TBB">
    <colname>COL 1</colname>
    <colname>COL 2</colname>
  </V1>
</datatable>

You would also have to be willing to have your table name nodes be attributes (you could always run a GREP or simple replace afterwards to make the node be valued) it would be close if not exactly the format you are looking for.

Sorry - that is probably not what you want to hear. But in a couple simple steps it can be done. Just not directly out of SQL Server in a single statement.