CTE to Build XML

  • Hi

    I have been experimenting with ways of extracting relational data in forms of xml from SQL Server 2005. I have created some views containg and ID integer field and a XML field. The ID field is what i filter on when selecting data, and the XML field contains the actual data.

    I came across a situation where the XML data was recursive in its nature. I have a product Hierarchy where products consists of a list of SubProducts. I solved this by creating a SQL Function which is called recursively in my view, retreiving all products that have the current product as parent. Something Like this:

    SELECT

    ProductID,

    ProductName,

    ParentID,

    dbo.GetSubProducts(ProductID)

    FROM

    view_Product

    This worked fine with small amount of data, but ended up being very slow when there was a medium amount of data.

    I tried refactoring my view to use CTE, cause it seems the way to go.

    The problem is i get a folder like structure, and not a XML like structure.

    Like this:

    Product1

    Product2

    Product1/Product2

    Product1/Product2/Product3

    Product1/Product2/Product4

    Product3

    Product4

    What I wanted was like this:

    Product1/Product2/Product3+Product4

    Product2/Product3+Product4

    Product3

    Product4

    I want one row per product having all subproducts beneath it. A complete tree when looking at a specific product no matter the level in the hierarchy.

    I have not posted any sql as i have no real solution to the issue.

    I hope you understand the problm anyhow.

    Michael

  • Michael, I'm posting this message a lot today, but it would be really helpful if you could post up some sample data and expected results.

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Yes i know that examples ease the job of helping.

    I left out the example, cause i wanted to see if anybody had been experimenting with the same type of query, and whether it was possible at all to to what i require.

    im working on posting a complete example, with expected result

  • I created a full example including tables, views and data required to test the SQL.

    -------------------------------------------------------------------------------------------------

    ---- DATA STRUCTURE PREPERATION -------------------------------------------------------------

    -------------------------------------------------------------------------------------------------

    -- Create a Test Schema

    CREATE SCHEMA TEST

    GO

    -- Create a table with parent/child relationship.

    CREATE TABLE [TEST].[Product](

    [ID] [int] NOT NULL,

    [Name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [ParentID] [int] NOT NULL,

    [ParentName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    CONSTRAINT [PK_TEST_Product_1] PRIMARY KEY CLUSTERED

    (

    [ID] ASC,

    [ParentID] ASC

    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    -- Populate table with sample data.

    INSERT INTO [TEST].[Product] VALUES (26,'TV',0, NULL)

    INSERT INTO [TEST].[Product] VALUES (43,'Buy Settopbox',47,'Settopbox Group')

    INSERT INTO [TEST].[Product] VALUES (47,'Settopbox Group',26,'TV')

    INSERT INTO [TEST].[Product] VALUES (48,'Rent Settopbox',47,'Settopbox Group')

    INSERT INTO [TEST].[Product] VALUES (82,'DR1',98,'Plus TV')

    INSERT INTO [TEST].[Product] VALUES (83,'DR2',98,'Plus TV')

    INSERT INTO [TEST].[Product] VALUES (84,'Sverige TV1',98,'Plus TV')

    INSERT INTO [TEST].[Product] VALUES (85,'Sverige TV2',98,'Plus TV')

    INSERT INTO [TEST].[Product] VALUES (88,'TV2',98,'Plus TV')

    INSERT INTO [TEST].[Product] VALUES (97,'Basic TV',100,'Standard TV Group')

    INSERT INTO [TEST].[Product] VALUES (98,'Plus TV',100,'Standard TV Group')

    INSERT INTO [TEST].[Product] VALUES (100,'Standard TV Group',26,'TV')

    INSERT INTO [TEST].[Product] VALUES (124,'Special TV Group',26,'TV')

    INSERT INTO [TEST].[Product] VALUES (152,'Oprettelse TV',26,'TV')

    INSERT INTO [TEST].[Product] VALUES (698,'SmileSport pakke',124,'Special TV Group')

    INSERT INTO [TEST].[Product] VALUES (699,'HD Package',124,'Special TV Group')

    INSERT INTO [TEST].[Product] VALUES (701,'Discovery Package',124,'Special TV Group')

    INSERT INTO [TEST].[Product] VALUES (702,'Kids Package',124,'Special TV Group')

    INSERT INTO [TEST].[Product] VALUES (82,'DR1', 97, 'Basic TV')

    INSERT INTO [TEST].[Product] VALUES (83,'DR2', 97, 'Basic TV')

    -- Create view that selects from the table and defines an xml column.

    CREATE VIEW [TEST].[view_Xml_Product]

    AS

    SELECT

    P.ID,

    P.[Name],

    P.ParentID,

    P.ParentName,

    (

    SELECT

    P.ID,

    P.[Name],

    P.ParentID,

    P.ParentName

    FOR XML PATH('Product'), TYPE

    )

    AS [xml]

    FROM [TEST].[Product] P WITH (NOLOCK)

    GO

    -------------------------------------------------------------------------------------------------

    -------------------------------------------------------------------------------------------------

    -------------------------------------------------------------------------------------------------

    -------------------------------------------------------------------------------------------------

    ---- THE RECUSIVE CTE THAT CREATES RECURSIVE XML DOCUMENTS ------------------------------

    -------------------------------------------------------------------------------------------------

    WITH ProductTree (ID, [Name], ParentID, ParentName, [Path], [xml])

    AS

    (

    SELECT

    P.ID,

    P.[Name],

    P.ParentID,

    P.ParentName,

    CAST(P.ID AS Varchar(200)) AS [Path],

    P.[xml] AS [xml]

    FROM [TEST].[view_Xml_Product] AS P WITH (NOLOCK)

    UNION ALL

    SELECT

    P2.ID,

    P2.[Name],

    P2.ParentID,

    P2.ParentName,

    CAST(RTRIM(P2.ID) + '->' + P3.[Path] AS varchar(200)) AS [Path],

    (

    SELECT

    P2.ID,

    P2.[Name],

    P2.ParentID,

    P2.ParentName,

    P3.[xml] AS SubProductList

    FOR XML PATH('Product'), TYPE

    ) AS SubProductList

    FROM [TEST].[view_Xml_Product] AS P2 WITH (NOLOCK)

    INNER JOIN ProductTree AS P3 ON P2.ID = P3.ParentID

    )

    SELECT * FROM ProductTree ORDER BY 1

    OPTION (MAXRECURSION 50)

    -------------------------------------------------------------------------------------------------

    -------------------------------------------------------------------------------------------------

    -------------------------------------------------------------------------------------------------

    When executing the CTE you recieve a "folder-like" structure as shown in the Path field. Actually the XML column has the same structure as the path column.

    Like this:

    Product1

    Product2

    Product1->Product2

    Product1->Product2->Product3

    Product1->Product2->Product4

    Product3

    Product4

    and the xml:

    <Product>

    <ID>26</ID>

    <Name>TV</Name>

    <ParentID>0</ParentID>

    <SubProductList>

    <Product>

    <ID>124</ID>

    <Name>Special TV Group</Name>

    <ParentID>26</ParentID>

    <ParentName>TV</ParentName>

    <SubProductList>

    <Product>

    <ID>701</ID>

    <Name>Discovery Package</Name>

    <ParentID>124</ParentID>

    <ParentName>Special TV Group</ParentName>

    </Product>

    </SubProductList>

    </Product>

    </SubProductList>

    </Product>

    What I wanted was like this:

    Product1/Product2/Product3+Product4

    Product2/Product3+Product4

    Product3

    Product4

    and the xml (not complete, but show the idea):

    <Product>

    <ID>26</ID>

    <Name>TV</Name>

    <ParentID>0</ParentID>

    <SubProductList>

    <Product>

    <ID>124</ID>

    <Name>Special TV Group</Name>

    <ParentID>26</ParentID>

    <ParentName>TV</ParentName>

    <SubProductList>

    <Product>

    <ID>698</ID>

    <Name>SmileSport pakke</Name>

    <ParentID>124</ParentID>

    <ParentName>Special TV Group</ParentName>

    </Product>

    <Product>

    <ID>699</ID>

    <Name>HD Package</Name>

    <ParentID>124</ParentID>

    <ParentName>Special TV Group</ParentName>

    </Product>

    <Product>

    <ID>702</ID>

    <Name>Kids Package</Name>

    <ParentID>124</ParentID>

    <ParentName>Special TV Group</ParentName>

    </Product>

    <Product>

    <ID>701</ID>

    <Name>Discovery Package</Name>

    <ParentID>124</ParentID>

    <ParentName>Special TV Group</ParentName>

    </Product>

    </SubProductList>

    </Product>

    <Product>

    <ID>100</ID>

    <Name>Standard TV Group</Name>

    <ParentID>26</ParentID>

    <ParentName>TV</ParentName>

    <SubProductList>

    <Product>

    <ID>97</ID>

    <Name>Basic TV</Name>

    <ParentID>100</ParentID>

    <ParentName>Standard TV Group</ParentName>

    </Product>

    <Product>

    <ID>98</ID>

    <Name>Plus TV</Name>

    <ParentID>100</ParentID>

    <ParentName>Standard TV Group</ParentName>

    </Product>

    </SubProductList>

    </Product>

    </SubProductList>

    </Product>

    I want one row per product having all subproducts beneath it. A complete tree when looking at a specific product no matter the level in the hierarchy.

    I hope you understand what I intend to do.

    Michael Vivet

  • I can't see exactly what you're trying to achieve but see if this helps

    CREATE FUNCTION dbo.GetSubTree(@ID int)

    RETURNS XML

    BEGIN RETURN

    (SELECT ID AS "ID",

    [Name] AS "Name",

    ParentID AS "ParentID",

    dbo.GetSubTree(ID)

    FROM [TEST].[view_Xml_Product]

    WHERE ParentID=@ID

    ORDER BY ID

    FOR XML PATH('Product'),ROOT('SubProductList'),TYPE)

    END

    WITH ProductTree (ID, [Name], ParentID, ParentName, [Path], [xml], [xml2])

    AS

    (

    SELECT

    P.ID,

    P.[Name],

    P.ParentID,

    P.ParentName,

    CAST(P.ID AS Varchar(200)) AS [Path],

    P.[xml] AS [xml],

    (SELECT P.ID AS "ID",

    P.[Name] AS "Name",

    P.ParentID AS "ParentID",

    dbo.GetSubTree(P.ID)

    FOR XML PATH('Product'),TYPE)

    FROM [TEST].[view_Xml_Product] AS P WITH (NOLOCK)

    UNION ALL

    SELECT

    P2.ID,

    P2.[Name],

    P2.ParentID,

    P2.ParentName,

    CAST(RTRIM(P2.ID) + '->' + P3.[Path] AS varchar(200)) AS [Path],

    (

    SELECT

    P2.ID,

    P2.[Name],

    P2.ParentID,

    P2.ParentName,

    P3.[xml] AS SubProductList

    FOR XML PATH('Product'), TYPE

    ) AS SubProductList,

    (SELECT P2.ID AS "ID",

    P2.[Name] AS "Name",

    P2.ParentID AS "ParentID",

    dbo.GetSubTree(P2.ID)

    FOR XML PATH('Product'),TYPE)

    FROM [TEST].[view_Xml_Product] AS P2 WITH (NOLOCK)

    INNER JOIN ProductTree AS P3 ON P2.ID = P3.ParentID

    )

    SELECT * FROM ProductTree ORDER BY 1

    OPTION (MAXRECURSION 50)

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Thanks i will look at it.

    Although it seems alot like my initial apprach using just a the view that i posted and a recursive function as you have written.

    I thought that was kinda slow, so i was looking for a faster approach.

    Ill use your code in my real-life database objects that contains more records.

    Then i can compare the performance.

  • Your solution works, but its slower than just creating a recursive function which is then selected as part of the select statement.

    Thank you for your effort.

    If you find another faster way than the recursive function please feel free to post.

    I am still investigating af better method.

  • Here's another approach generating the full XML once

    CREATE FUNCTION dbo.GetSubTree(@ID INT,@Path VARCHAR(MAX))

    RETURNS XML

    BEGIN RETURN

    (SELECT ID AS "ID",

    [Name] AS "Name",

    ParentID AS "ParentID",

    @Path + '->' + CAST(ID AS VARCHAR(MAX)) AS "Path",

    dbo.GetSubTree(ID,@Path + '->' + CAST(ID AS VARCHAR(MAX)))

    FROM [TEST].[view_Xml_Product]

    WHERE ParentID=@ID

    ORDER BY ID

    FOR XML PATH('Product'),ROOT('SubProductList'),TYPE)

    END

    WITH AllNodes(a) AS (

    SELECT ID AS "ID",

    [Name] AS "Name",

    ParentID AS "ParentID",

    ID AS "Path",

    dbo.GetSubTree(ID,cast(ID as varchar(max)))

    FROM [TEST].[view_Xml_Product]

    WHERE ParentID=0

    FOR XML PATH('Product'),TYPE)

    SELECT r.value('ID[1]','INT') AS ID,

    r.value('Name[1]','VARCHAR(20)') AS Name,

    r.value('ParentID[1]','INT') AS ParentID,

    r.value('../../Name[1]','VARCHAR(20)') AS ParentName,

    r.value('Path[1]','VARCHAR(100)') AS Path,

    r.query('.') AS xml

    FROM AllNodes

    CROSS APPLY a.nodes('//Product') as x(r)

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 8 posts - 1 through 7 (of 7 total)

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