Create structured (xml) document from hierarchical table data (T-SQL)

Question

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

Inserts:

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

And I would like to generate an xml file from it, which is structured according to the parent identifiers

like this (simplified)

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

I believe this is done, perhaps with some kind of recursion or something like that, but I don’t know how to do it. Any help is much appreciated!

edit: its SQL Server Express 2008

I don't care if this is XML or not, because I only use it to populate the treeview control.

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

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

Answer 1

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

1. Create a recursive function

 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. Create a SELECT statement that calls the function

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

This creates hierarchical XML, no matter how deep this tree 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> 

Answer 2

You can also do this without creating a separate function by including the subquery as an additional column that returns XML. For example, the following returns a hierarchical XML document containing users and a list of roles associated with them:

 SELECT FirstName, LastName, CONVERT(XML, (SELECT r.UserID, r.RoleID FROM global.[UserRole] r WHERE r.USerID = [user].UserID FOR XML RAW ('Role'), ELEMENTS, root('Roles') )) FROM global.[user] FOR XML RAW ('User'), ELEMENTS, root('Users')