我有这样的表(简化)：
@H_403_2@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：
@H_403_2@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))插入内容：
@H_403_2@INSERT INTO [tblIndustryCodes] ([IndustryCode],[IndustryName],[ParentId]) VALUES ('IND','Industry',NULL),('PHARM','Pharmacy',1),('FIN','Finance',('CFIN','Corporate Finance',3),('CMRKT','Capital Markets',4)像这样(简化)
@H_403_2@<IND> <AUT> <PHA> <CON> <FIN> <CFIN> <CMRKT>我相信它的完成可能是某种类型的递归或类似的东西,但我不知道如何.任何帮助是极大的赞赏！
编辑：它是sql Server Express 2008
我真的不在乎它是否是有效的XML,因为我只使用它来填充树视图控件.
edit2：我可能会使用“FOR XML EXPLICIT”,但是当没有固定的树最大深度时,我真的不理解语法.
edit3：为了更容易理解任务,我为表添加了DDL
解决方法
根据Recep的回答(见评论),我为这个问题创建了以下解决方案：
1.创建递归函数
@H_403_2@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 @H_403_2@SELECT IdIndustry AS "@key",'' AS "@parentkey",dbo.SelectChild(IdIndustry) FROM dbo.tblIndustryCodes WHERE ParentId is null FOR XML PATH ('record')无论树实际有多深,这都会创建一个分层XML：
@H_403_2@<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>