generate XML document from hierarchical table data

Question

generate XML document from hierarchical table data

Michael Niemand

Ten Centuries

Points: 1149
More actions
March 9, 2010 at 7:49 am

#230602

Hello people!
I have a table like this:
[font="Courier New"]
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
[/font]
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)
And I'd like to generate a xml file from it which is structured according to the parent IDs
like this (simplified)
<IND>
--<AUT>
--<PHA>
<CON>
<FIN>
--<CFIN>
----<CMRKT>
Its a SQL Server Express 2008
I tried FOR XM explicit (without success) The tree depth is theoretically unlimited, so there has to be some kind of recursion I guess.
I just played around with some SQL, but none of it worked at all. I never used FOR XML before and all the examples I found had a pre-defined max tree depth.
Any help is greatly appreciated!

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Please post the DDL (CREATE TABLE statement) for the table, sample data (as a series of INSERT INTO statements), and the code you have written in attempting to solve your problem.

Michael Niemand Ten Centuries Points: 1149 More actions · Answer 2

Here's what folks @ stackoverflow answered, which I found is (partly) working:

1. create a recursive function:

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 call the function in your select:

select

[Key] as "@key",

'' as "@parentkey",

dbo.SelectChild([Key])

from KEY_TABLE

where [ParentKey] is null

for xml path ('record')

Carl Federl One Orange Chip Points: 25384 More actions · Answer 3

The schema uses the design pattern named "Adjacency List" and needs to be converted with a recursive CTE to a "Path Enumeration" pattern before converting to XML.

With IndustryCodes_Path

([IndustryCode],[IdIndustry], LevelCnt, [IndustryName], PathEnum)

as

(SELECT[tblIndustryCodes].[IndustryCode]

,[tblIndustryCodes].[IdIndustry]

,1 AS LevelCnt

,[tblIndustryCodes].[IndustryName]

,CAST([tblIndustryCodes].[IdIndustry] as varchar(4000) ) as PathEnum

FROM[tblIndustryCodes]

WHERE[ParentId] is null

UNION ALL

SELECT[tblIndustryCodes].[IndustryCode]

,[tblIndustryCodes].[IdIndustry]

,LevelCnt + 1 as LevelCnt

,[tblIndustryCodes].[IndustryName]

,CAST( PathEnum + '\' + cast([tblIndustryCodes].[IdIndustry] as varchar(255) ) as varchar(4000) )

as PathEnum

FROMIndustryCodes_Path

JOIN[tblIndustryCodes]

on [tblIndustryCodes].[ParentId]= IndustryCodes_Path.[IdIndustry]

)

select [IndustryCode]

fromIndustryCodes_Path

order by PathEnum

FOR XML AUTO;

SQL = Scarcely Qualifies as a Language

Michael Niemand Ten Centuries Points: 1149 More actions · Answer 4

thanks for your answer! But unfortunately this returns

<IndustryCodes_Path IndustryCode="IND" />

<IndustryCodes_Path IndustryCode="AUTO" />

<IndustryCodes_Path IndustryCode="PHARM" />

<IndustryCodes_Path IndustryCode="FIN" />

<IndustryCodes_Path IndustryCode="CFIN" />

<IndustryCodes_Path IndustryCode="CMRKT" />

<IndustryCodes_Path IndustryCode="CON" />

<IndustryCodes_Path IndustryCode="IMPL" />

<IndustryCodes_Path IndustryCode="STRAT" />

which is a flat hierarchy. I need the items like

<IndustryCodes_Path IndustryCode="IND">

<IndustryCodes_Path IndustryCode="AUTO" />

<IndustryCodes_Path IndustryCode="PHARM" />

</IndustryCodes_Path>

<IndustryCodes_Path IndustryCode="FIN">

<IndustryCodes_Path IndustryCode="CFIN">

<IndustryCodes_Path IndustryCode="CMRKT" />

</IndustryCodes_Path>

<IndustryCodes_Path IndustryCode="CON">

<IndustryCodes_Path IndustryCode="IMPL" />

<IndustryCodes_Path IndustryCode="STRAT" />

</IndustryCodes_Path>

Hunterwood SSCrazy Points: 2074 More actions · Answer 5

This thread is about a similar problem. Perhaps some of the suggested solutions there can be of any help...

http://www.sqlservercentral.com/Forums/Topic879842-338-1.aspx

/Markus

Michael Niemand Ten Centuries Points: 1149 More actions · Answer 6

thanks for the info!

I already answered the question myself based on a thread on stackoverflow.com

--> http://stackoverflow.com/questions/2409228/generate-structured-xml-document-from-hierarchical-table-data-t-sql

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

hemanulvi SSC Enthusiast Points: 132 More actions · Answer 7

I tried your solution it works for multiple child leve hierarchy, however for siblings it doesnt seem to work

my desired output is as below:

<?xml version="1.0" encoding="utf-8"?>

</Parentrecord>

</child>

</child>

</child>

</Parentrecord>

</Parentrecord>

</records>

Could you help please

Michael Niemand Ten Centuries Points: 1149 More actions · Answer 8

I don't really understand, what you are trying to achieve. Could you elaborate?

hemanulvi SSC Enthusiast Points: 132 More actions · Answer 9

Hi I require to take the table fields into a multilevel xml.

For example,

I have two tables

Table 1: Codes

Code ID | Code Value | Code Group | Code Desc

1 Manager HR manages work

2 Assistant HR

3 Employee1 Employee

4 Employee2 Employee

5 Sys Admin1 IT

6 Sys Admin2 IT

7 admin 1 Admin

8 admin 2 Admin

Table 2 : MappingTable

MapID | Code ID (FK) | ParentCodeID (FK)

1 1 NULL

2 2 NULL

3 3 1

4 4 1

5 5 1

6 6 1

7 7 3

8 8 4

So my XML should be looking like as below

<Codes>

<CodeGroup group="HR">

<Code CodeID="1" CodeValue="Manager" codeGroup="HR">

<ChildNodes group="Employee">

<Code codeID="3" CodeValue="employee1" codeGroup="Employee">

<ChildNodes group="Admin">

<Code codeID="7" CodeValue="admin1" codeGroup="Admin"/>

</ChildNodes>

</Code>

<Code codeID="4" CodeValue="employee2" codeGroup="Employee">

<ChildNodes group="Admin">

<Code codeID="7" CodeValue="admin1" codeGroup="Admin"/>

</ChildNodes>

</Code>

</ChildNodes>

<ChildNodes group="IT">

<Code codeID="5" CodeValue="sys Admin1" codeGroup="IT"/>

<Code codeID="6" CodeValue="sys Admin2" codeGroup="IT"/>

</ChildNodes>

</Code>

</CodeGroup>

</Codes>

With your above code I am able to get most of this structure, but I am not able to get the Employee and IT nodes grouped together under different ChildNodes of HR, they all come under single node directly under HR

I hope this is clear : This is what I am currently getting now

<Codes>

<CodeGroup group="HR">

<Code CodeID="1" CodeValue="Manager" codeGroup="HR">

<Code codeID="3" CodeValue="employee1" codeGroup="Employee">

<ChildNodes group="Admin">

<Code codeID="7" CodeValue="admin1" codeGroup="Admin"/>

</ChildNodes>

</Code>

<Code codeID="4" CodeValue="employee2" codeGroup="Employee">

<ChildNodes group="Admin">

<Code codeID="7" CodeValue="admin1" codeGroup="Admin"/>

</ChildNodes>

</Code>

<Code codeID="5" CodeValue="sys Admin1" codeGroup="IT"/>

<Code codeID="6" CodeValue="sys Admin2" codeGroup="IT"/> </Code>

</CodeGroup>

</Codes>

Please help

Michael Niemand Ten Centuries Points: 1149 More actions · Answer 10

You should be able to accomplish this by adding an IF to the function and an additional column like "IsGroup" to your table.

HR and IT have IsGroup=true. (Or somethin like isPerson=False)

Within the function you check if the current row has the IsGroup Bit set or not and then create a group or standard entry.

Can't proveide code atm, maybe you try that out and see how far you make it ... I'm a bit in a hurry. Good luck!

hemanulvi SSC Enthusiast Points: 132 More actions · Answer 11

I tried this SP

SELECT codeMappingTable.codeID AS "@codeID",

codes.codeLiteral AS "@codeLiteral",

codes.codeGroup AS "@codeGroup"

,dbo.GetPartsSubTree(codeMappingTable.codeID)

FROM codeMappingTable,codes

WHERE codeMappingTable.ParentcodeID IS NULL AND codes.codeID = codeMappingTable.codeID

ORDER BY codes.codeGroup,codes.codeLiteral

FOR XML PATH('CodeParent'),ROOT('Codes'),TYPE

And Recursive function

ALTER FUNCTION [dbo].[GetPartsSubTree](@PartNumberID int)

RETURNS XML

BEGIN RETURN

(SELECT codeMappingTable.codeID AS "@codeID",

codes.codeLiteral AS "@codeLiteral",

codes.codeGroup AS "@codeGroup",

dbo.GetPartsSubTree(codeMappingTable.codeID)

FROM codeMappingTable,codes

WHERE codeMappingTable.ParentcodeID=@PartNumberID and codes.codeID = codeMappingTable.codeID

ORDER BY codes.codeGroup

FOR XML PATH('CodeParent'),ROOT('ChildGroup'),TYPE)

END

It gives me repeating nodes and If a parent has two types of children all come under common nodes under that parent the children are not categorised by thier type.. Please help

Michael Niemand Ten Centuries Points: 1149 More actions · Answer 12

please provide some sample data as INSERTS, I will look over it when I'm in the office the next time (Wednesday I believe)

hemanulvi SSC Enthusiast Points: 132 More actions · Answer 13

Please find the sample data

CREATE TABLE dept

(

codeID INT,

codeValue VARCHAR(50),

codeGroup VARCHAR(50)

)

INSERT dept

SELECT 1,'MANAGER','HR' UNION ALL

SELECT 3,'EMPLOYEE1','EMPLOYEE' UNION ALL

SELECT 7,'ADMIN1','ADMIN' UNION ALL

SELECT 4,'EMPLOYEE2','EMPLOYEE' UNION ALL

SELECT 5,'SYS ADMIN1','IT' UNION ALL

SELECT 6,'SYS ADMIN2','IT' UNION ALL

SELECT 2,'IDM1','INFRASTRUCTURE'

create TABLE debtMapping

(

mapID INT,

codeID INT,

parentCodeID INT

)

INSERT debtMapping

SELECT 1,1,NULL UNION ALL

SELECT 2,3,1 UNION ALL

SELECT 3,7,3 UNION ALL

SELECT 4,4,1 UNION ALL

SELECT 5,7,4 UNION ALL

SELECT 6,5,1 UNION ALL

SELECT 7,6,1 UNION ALL

SELECT 8,2,NULL

There is one more problem happening when I have 1000 records in the mapping table, the select statement in the stored procedure is probably duplicating the results so I get the same xml nodes duplicated thrice, I am not able to replicate it with small data. Please let me know if you need any more information.

hemanulvi SSC Enthusiast Points: 132 More actions · Answer 14

I am able to rectify the duplicate record issue, now the only issue left is the xml format displayed, I want the main nodes to be grouped under <codeGroup node> and the Child nodes to be grouped under each <Child nodes group> the group is the factor to use to group elements.