generate XML document from hierarchical table data

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

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

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

  • 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

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

    <IndustryCodes_Path IndustryCode="CON">

    <IndustryCodes_Path IndustryCode="IMPL" />

    <IndustryCodes_Path IndustryCode="STRAT" />

    </IndustryCodes_Path>

  • 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

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

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

  • 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"?>

    <records>

    <Parentrecord key="1" parentkey="" Code="IND" Name="Industry">

    <Child key="2" parentkey="1" Code="AUTO" Name="Automotive" />

    <Child key="3" parentkey="1" Code="PHARM" Name="Pharmaceuticals" />

    </Parentrecord>

    <Parentrecord key="4" parentkey="" Code="FIN" Name="Finance">

    <child key="5" parentkey="4" Code="CFIN" Name="Corporate Finance">

    <SubChild key="6" parentkey="5" Code="CMRKT" Name="Capital Markets" />

    </child>

    <child key="10" parentkey="4" Code="xyzn" Name="Corporate tele">

    <SubChild key="12" parentkey="10" Code="xysb" Name="tele business" />

    </child>

    <child key="11" parentkey="4" Code="abcd" Name="Corporate IT">

    <SubChild key="13" parentkey="11" Code="fghj" Name="IT hub" />

    </child>

    </Parentrecord>

    <Parentrecord key="7" parentkey="" Code="CON" Name="Cosulting">

    <child key="8" parentkey="7" Code="IMPL" Name="Implementation" />

    <child key="9" parentkey="7" Code="STRAT" Name="Strategy" />

    </Parentrecord>

    </records>

    Could you help please

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

  • 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

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

  • 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

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

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

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

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

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