Transform SQL table to XML with column as parent node?

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( '' + (SELECT c.

Column_name as 'CN' FROM a c WHERE c. Table_name = p. Table_name FOR XML PATH('')) + '' AS XML) FROM a p GROUP BY p.

Table_name FOR XML PATH(''), ROOT('datatable') Produces: datatable> COLUMN_1 COLUMN_2 COLUMN_1 COLUMN_2.

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( '' + (SELECT c.

Column_name as 'CN' FROM a c WHERE c. Table_name = p. Table_name FOR XML PATH('')) + '' AS XML) FROM a p GROUP BY p.

Table_name FOR XML PATH(''), ROOT('datatable') Produces: COLUMN_1 COLUMN_2 COLUMN_1 COLUMN_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 or a value TABLE_A but you can't get 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: COL 1 COL 2 COL 1 COL 2 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.

It is possible, you need to name columns with path. Like this: 'parent\child'. Try this: select ( select * from ( select 'COLUMN_1' 'cn' UNION ALL select 'COLUMN_2' 'cn' ) as t for xml path(''), root('TABLE_A'), type ) ,( select * from ( select 'COLUMN_1' 'cn' UNION ALL select 'COLUMN_2' 'cn' ) as t for xml path(''), root('TABLE_B'), type ) for xml path(''), ROOT('datatable').

Sorry, I need a production solution that will work on large table inputs without the need to hard code things like root('TABLE_A'). – sqlconsumer. Net Sep 22 '10 at 15:27 The more I think about it the more I think it can't be done.

I'm asking for the data in my first column to represent node names and the data in my second column to represent node values. – sqlconsumer. Net Sep 22 '10 at 15:28 @sqlconsumer.Net you can build any kind of xml as varchar and then cast it as xml – Denis Valeev Sep 22 '10 at 16:06 I'm only producing clean code in the way it was intended.

Playing arround with vast string manipulation processes is not the answer. Understanding the capabilities of the FOR XML clause is the objective. – sqlconsumer.

Net Sep 22 '10 at 9:01.

Check this verly lenghty article on the sql to xml conversions. Hope this will help you - stylusstudio.com/sqlxml_tutorial.html.

Sadly it has nothing to do with the sql server. – Denis Valeev Jun 29 at 19:42.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.