T-SQL Question: Query in XML

Question

can anyone show me how to generate from this data

------------------------DATA-------------------------- Key ParentKey 5 NULL 25 5 33 25 26 5 27 5 34 27 28 5 29 5 

to this XML result?

 ---------------------RESULTS-------------------------- <record key="5" parentkey = ""> <record key="25" parentkey = "5"> <record key="33" parentkey = "25"></record> </record> </record> <record key="25" parentkey = "5"> <record key="26" parentkey = "5"> <record key="27" parentkey = "5"> <record key="34" parentkey = "27"></record> </record> </record> <record key="28" parentkey = "5"> <record key="29" parentkey = "5"> </record> 

Answer 1

You can build almost any XML using FOR XML PATH.

In this case, if you need 2 levels:

 select [Key] as "@key", '' as "@parentkey", (select [Key] as "@key", [ParentKey] as "@parentkey" from KEY_TABLE t1 where [ParentKey] = t.[Key] for xml path('record'), type) from KEY_TABLE t where [ParentKey] is null for xml path ('record') 

for 3 levels, you need to write another subquery, for example:

 select [Key] as "@key", '' as "@parentkey", (select [Key] as "@key", [ParentKey] as "@parentkey", (select [Key] as "@key", [ParentKey] as "@parentkey" from KEY_TABLE t2 where [ParentKey] = t1.[Key] for xml path('record'), type) from KEY_TABLE t1 where [ParentKey] = t.[Key] for xml path('record'), type) from KEY_TABLE t where [ParentKey] is null for xml path ('record') 

must do it.

---

A subquery can be easily reorganized into a recursive function like:

 create function SelectChild(@key as int) returns xml begin return ( select [Key] as "@key", [ParentKey] as "@parentkey", dbo.SelectChild([Key]) from KEY_TABLE where [ParentKey] = @key for xml path('record'), type ) end 

Then you can get what you need,

 select [Key] as "@key", '' as "@parentkey", dbo.SelectChild([Key]) from KEY_TABLE where [ParentKey] is null for xml path ('record') 

Answer 2

 select 1 AS TAG, record AS [key!1!parentkey] from table_keys FOR XML EXPLICIT 

must do it.