T-SQL Question : Query to XML

Question

anyone can 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

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

should do it.

Answer 2

You can construct just about any XML using FOR XML's PATH mode.

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 one more subquery, something like:

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

should do it.

---

The subquery can easily be refactored into a recursive function as:

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 with

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