T-SQL 问题:查询到 XML [英] 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

这个 XML 结果?

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

推荐答案

您可以使用 FOR XML 的 PATH 模式构建几乎任何 XML.

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

在这种情况下，如果您需要 2 个级别:

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

对于 3 个级别，您需要再编写一个子查询，例如:

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

应该这样做.

子查询可以很容易地重构为递归函数:

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

这篇关于T-SQL 问题:查询到 XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！