从分层表数据 (T-SQL) 生成结构化 (xml) 文档 [英] generate structured (xml) document from hierarchical table data (T-SQL)

问题描述

我有一张这样的表格(简化版):

I have a table like this (simplified):

ID   |   Name  |   Parent
---------------------------------
1    |  IND    |   NULL
2    |  INS    |   5
3    |  CON    |   NULL
4    |  AUT    |   1
5    |  FIN    |   NULL
6    |  PHA    |   1
7    |  CFIN   |   5
8    |  CMRKT  |   7

DDL:

CREATE TABLE [dbo].[tblIndustryCodes](
        [IdIndustry] [int] IDENTITY(1,1) NOT NULL,
        [IndustryCode] [nvarchar](5) NULL,
        [IndustryName] [nvarchar](50) NULL,
        [ParentId] [int] NULL,
 CONSTRAINT [PK_tblIndustryCodes] PRIMARY KEY CLUSTERED (       [IdIndustry] ASC))

插入:

INSERT INTO [tblIndustryCodes]
           ([IndustryCode]
           ,[IndustryName]
           ,[ParentId])
     VALUES
           ('IND','Industry',NULL),
           ('PHARM','Pharmacy',1),
           ('FIN','Finance',NULL),
           ('CFIN','Corporate Finance',3),
           ('CMRKT','Capital Markets',4)

我想从中生成一个根据父 ID 构建的 xml 文件

And I'd like to generate a xml file from it which is structured according to the parent IDs

像这样(简化)

<IND>
   <AUT>
   <PHA>
<CON>
<FIN>
   <CFIN>
      <CMRKT>

我相信它可能通过某种递归或类似的东西完成，但我不知道如何.非常感谢任何帮助！

I believe its done maybe with some kinda recursion or something like that, but I don't know how. Any help is greatly appreciated!

它是一个 SQL Server Express 2008

edit: Its a SQL Server Express 2008

我并不关心它是否是有效的 XML，因为我只用它来填充树视图控件.

I don't really care if it's valid XML or not, because I only use it to populate a treeview control.

edit2:我可能会使用FOR XML EXPLICIT"，但是当树没有固定的最大深度时，我并不真正理解语法.

edit2: I would probably use "FOR XML EXPLICIT" but I don't really understand the syntax when there is no fixed maximum depth of the tree.

edit3:为了更容易理解任务，我为表添加了 DDL

edit3: for easier understanding of the task, I added the DDL for the table

推荐答案

根据 Recep 的回答(见评论)，我为这个问题创建了以下解决方案:

Based on Recep's answer (see comments) I created the following solution for this problem:

1.创建递归函数

CREATE function SelectChild(@key as int)
returns xml
begin
    return (
        select 
            IdIndustry as "@key", 
            ParentId as "@parentkey",
            IndustryCode as "@Code",
            IndustryName as "@Name",
            dbo.SelectChild(IdIndustry)
        from tblIndustryCodes
        where ParentId = @key
        for xml path('record'), type
    )
end

2.构建一个 SELECT 语句，调用函数

SELECT 
    IdIndustry AS "@key", 
    '' AS "@parentkey",
    IndustryCode as "@Code",
    IndustryName as "@Name",
    dbo.SelectChild(IdIndustry)     
FROM dbo.tblIndustryCodes
WHERE ParentId is null
FOR XML PATH ('record')

这将创建一个分层的 XML，无论树实际上有多深:

This creates a hierarchical XML, no matter how deep the tree actually is:

<record key="1" parentkey="" Code="IND" Name="Industry">
  <record key="2" parentkey="1" Code="AUTO" Name="Automotive" />
  <record key="3" parentkey="1" Code="PHARM" Name="Pharmaceuticals" />
</record>
<record key="4" parentkey="" Code="FIN" Name="Finance">
  <record key="5" parentkey="4" Code="CFIN" Name="Corporate Finance">
    <record key="6" parentkey="5" Code="CMRKT" Name="Capital Markets" />
  </record>
</record>
<record key="7" parentkey="" Code="CON" Name="Cosulting">
  <record key="8" parentkey="7" Code="IMPL" Name="Implementation" />
  <record key="9" parentkey="7" Code="STRAT" Name="Strategy" />
</record>

这篇关于从分层表数据 (T-SQL) 生成结构化 (xml) 文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！