T-SQL Question : Query to XML
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>
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')
select 1 AS TAG, record AS [key!1!parentkey] from table_keys FOR XML EXPLICIT
should do it.
精彩评论