FOR XML and trying to avoid nested cursors

  • Alright, history first. I've got a bit of a mess I've inherited. Groups inheriting groups inheriting... you get the drift. Each of these groups have external attributes that also need to be considered, but I'm not at that point yet. Right now, I'm trying to get a collar on the group hierarchy itself. Antiques littered with obsoletes have caused a reality that the business is just creating new groups to avoid the pitfalls that any old group my bring up.

    This is primarily because the vendor app can't find a good way to display the hierarchy to the users. Avoiding that entire discussion, I'm trying to build something that I can personally use to troubleshoot with, and eventually put into a position that my business users can troubleshoot themselves.

    So, what's the deal? I've got 'n' level hierarchies in this mess with a theoretical lack of an upper bound. Realistically it's seven because the software will otherwise crap out but I don't trust the historicals. There's more than likely forgotten hierarchies stuffed in there somewhere. Add to this the hierarchy is NOT org-chart particular. A subgroup can belong to multiple parents. If anything this is similar to a Bill of Materials chart. IE: The wheels go on both cars and trucks, the wheels have multiple components, one of which is bolts, and bolts go all over the danged place. And you can't trust a particular component to always exist at a particular tier of the hierarchy.

    My current intent is to build myself an XML document I can stuff into IE and collapse/expand/search nodes to figure out what all is affected when a particular group is selected. See, whenever you select one, it unrolls in the app to all underlying levels. So I need to be able to get a full node list of all the underlying pieces.

    That XML is killing me, because it pivots EVERYTHING to be usable as a node tree. I'm hoping you guys can give me a bit of a hand.

    First, some sample data to work with:

    IF OBJECT_ID('tempdb..#RecursiveGroupFinding') IS NOT NULL

    DROP TABLE #RecursiveGroupFinding

    IF OBJECT_ID('tempdb..#GroupList') IS NOT NULL

    DROP TABLE #GroupList

    CREATE TABLE #RecursiveGroupFinding

    (ParentIDINTNOT NULL,

    ChildIDINTNOT NULL

    )

    CREATE TABLE #GroupList

    (GroupIDINTIDENTITY(1,1) NOT NULL,

    GroupNameVARCHAR(30) NOT NULL

    )

    CREATE CLUSTERED INDEX idx_c_RecursiveGroupFinding ON #RecursiveGroupFinding (ParentID, ChildID)

    CREATE CLUSTERED INDEX idx_c_GroupList ON #GroupList (GroupID)

    INSERT INTO #GroupList

    VALUES ('Parent1'),('Parent2'),('Child1'),('Child2'),('Child3'),('SubChild1'),('Subchild2'),('Subchild3'),('Icing')

    INSERT INTO #RecursiveGroupFinding

    VALUES ( 1, 3), (1, 4), (2, 3),(2,5), (3, 6), (3, 7), (4, 8), (6, 9), (7,9)

    Simple enough, you get this as a result list:

    SELECT

    g.GroupName AS Parent,

    g2.GroupName AS Child

    FROM

    #RecursiveGroupFinding AS gf

    JOIN

    #GroupList AS g

    ONgf.ParentID = g.GroupID

    JOIN

    #GroupList AS g2

    ONgf.ChildID = g2.GroupID

    Parent1Child1

    Parent1Child2

    Parent2Child1

    Parent2Child3

    Child1SubChild1

    Child1Subchild2

    Child2Subchild3

    SubChild1Icing

    Subchild2Icing

    Now, the code I've got so far illustrates a few of the uglies I'm arguing with:

    SELECT

    RootLevel.GroupName,

    Tier1.GroupName AS T1GroupName,

    Tier2.GroupName AS T2GroupName,

    Tier3.GroupName AS T3GroupName,

    Tier4.GroupName AS T4GroupName

    FROM

    (SELECT

    g.GroupID,

    g.GroupName

    FROM

    #GroupList AS g

    LEFT JOIN

    #RecursiveGroupFinding AS gf

    ONg.GroupID = gf.ChildID

    WHERE

    gf.ChildID IS NULL

    ) AS RootLevel

    OUTER APPLY (SELECT g.GroupID, g.GroupName

    FROM #GroupList AS g

    JOIN #RecursiveGroupFinding AS gf

    ONg.GroupID = gf.ChildID

    WHERE gf.ParentID = RootLevel.GroupID

    ) AS Tier1

    OUTER APPLY (SELECT g.GroupID, g.GroupName

    FROM #GroupList AS g

    JOIN #RecursiveGroupFinding AS gf

    ONg.GroupID = gf.ChildID

    WHERE gf.ParentID = Tier1.GroupID

    ) AS Tier2

    OUTER APPLY (SELECT g.GroupID, g.GroupName

    FROM #GroupList AS g

    JOIN #RecursiveGroupFinding AS gf

    ONg.GroupID = gf.ChildID

    WHERE gf.ParentID = Tier2.GroupID

    ) AS Tier3

    OUTER APPLY (SELECT g.GroupID, g.GroupName

    FROM #GroupList AS g

    JOIN #RecursiveGroupFinding AS gf

    ONg.GroupID = gf.ChildID

    WHERE gf.ParentID = Tier3.GroupID

    ) AS Tier4

    FOR XML AUTO

    There are rCTE methods out there for BoM I can use but they 'stack' the results. Pivoting ends up looking like this. I'm not AGAINST a pivot per se, but the part I can't seem to kick is the results that look like this:

    <RootLevel GroupName="Parent1">

    <Tier1 T1GroupName="Child1">

    <Tier2 T2GroupName="SubChild1">

    <Tier3 T3GroupName="Icing">

    <Tier4 />

    </Tier3>

    </Tier2>

    <Tier2 T2GroupName="Subchild2">

    <Tier3 T3GroupName="Icing">

    <Tier4 />

    </Tier3>

    </Tier2>

    </Tier1>

    <Tier1 T1GroupName="Child2">

    <Tier2 T2GroupName="Subchild3">

    <Tier3>

    <Tier4 />

    </Tier3>

    </Tier2>

    </Tier1>

    </RootLevel>

    <RootLevel GroupName="Parent2">

    <Tier1 T1GroupName="Child1">

    <Tier2 T2GroupName="SubChild1">

    <Tier3 T3GroupName="Icing">

    <Tier4 />

    </Tier3>

    </Tier2>

    <Tier2 T2GroupName="Subchild2">

    <Tier3 T3GroupName="Icing">

    <Tier4 />

    </Tier3>

    </Tier2>

    </Tier1>

    <Tier1 T1GroupName="Child3">

    <Tier2>

    <Tier3>

    <Tier4 />

    </Tier3>

    </Tier2>

    </Tier1>

    </RootLevel>

    Please note all the extraneous tiers because of non-existant data but columns needing to exist.

    "Craig, get to the question!!" Errr, yeah, sorry, rambling a bit. Was hoping as I typed this out the answer would come to me, but it hasn't. Here's what I'd like to do:

    1) Turn the above node list into <Group>Parent1<Group>Child1</Group></Group> (etc...). Note this is impossible with named columns in the select list.

    2) Make this n-tier recursion.

    3) Remove extraneous tier levels.

    4) Avoid my last recourse... recursive cursors.

    The only solution I can see to this is nesting cursors via proc executions and passing a VARCHAR(MAX) around to build out the XML exactly as I want it.

    I'm rather open to suggestions on avoiding that... Also, if my google-fu has just failed me utterly (Bill of Materials XML Node list being one of my search patterns) please point me in the right direction with a simple lmgtfy. I can find plenty of VB/C# code to get the result I want, but I can't find anything at the proc level and I'd rather not have to dump this entire tableset through the pipes to a front end for them to bubblesort (orwhatever) the records together to build the hierarchy... I'm also not that good at it and I don't have an app coder to spare.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Kraig, I feel your pain: been there.

    First, IMO BoM is a wrong model for this one: in BoM you can have the same element on several different levels. For example, you may use the 8 #13 bolts to assemble an automatic transmission, then you use another 8 of the same bolts to mate the transmission to the engine, and finally use 10 bolts to attach the assembly to the frame of the car.

    This is not your case, I believe. Please correct me if I am wrong.

    I would use CLR Integration and the power of XML LINQ to handle this, in a few lines of C# code. Not every SQLS installation allows that; if yours does, being in your shoes, I would go for it.

    I can whip that code up for you. It might cost you a drink.

  • Revenant (3/3/2013)


    First, IMO BoM is a wrong model for this one: in BoM you can have the same element on several different levels. For example, you may use the 8 #13 bolts to assemble an automatic transmission, then you use another 8 of the same bolts to mate the transmission to the engine, and finally use 10 bolts to attach the assembly to the frame of the car.

    This is not your case, I believe. Please correct me if I am wrong.

    In theory, you're right about BoM using the same component at multiple levels within the same hierarchy being inaccurate to this case. At something in the range of 60,000 actual hierarchies to review, I'm not sure yet.

    I would use CLR Integration and the power of XML LINQ to handle this, in a few lines of C# code. Not every SQLS installation allows that; if yours does, being in your shoes, I would go for it.

    I can whip that code up for you. It might cost you a drink.

    Unfortunately, my current location doesn't have SQL CLR activated, nor do I have the political coin to attempt to push it through for a non business critical item. I appreciate the offer though Revenant. If I ever get up in your area though, I'll still stand you that drink, just to pick your brain and hang with another SSC'er. 😀


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Hi Craig

    I've had a go with your example data using the following and it appears to do what you require ... well the removing of empty tiers

    I hope it works for you and I haven't messed up your requirement

    ;with rcte as (

    select ParentId ID, null ParentID, 1 HLevel

    ,ParentID RootID

    ,null tier1ID

    ,null tier2ID

    ,null tier3ID

    ,null tier4ID

    ,null tier5ID

    from #RecursiveGroupFinding gp

    where not exists (select 1 from #RecursiveGroupFinding gc where gp.parentid = gc.childid)

    group by gp.Parentid

    union all

    select gc.ChildID, r.id, HLevel + 1

    ,RootID RootID

    ,case when hlevel = 1 then gc.ChildID else tier1ID end tier1ID

    ,case when hlevel = 2 then gc.ChildID else tier2ID end tier2ID

    ,case when hlevel = 3 then gc.ChildID else tier3ID end tier3ID

    ,case when hlevel = 4 then gc.ChildID else tier4ID end tier4ID

    ,case when hlevel = 5 then gc.ChildID else tier5ID end tier5ID

    from #RecursiveGroupFinding gc

    inner join rcte r on r.id = gc.parentid

    )

    select Hlevel [tag], Case when HLevel - 1 = 0 then null else HLevel - 1 end [parent]

    ,case when Hlevel = 1 then GroupName else null end [RootLevel!1!GroupName]

    ,case when Hlevel = 2 then GroupName else null end [Tier1!2!T1GroupName]

    ,case when Hlevel = 3 then GroupName else null end [Tier2!3!T2GroupName]

    ,case when Hlevel = 4 then GroupName else null end [Tier3!4!T3GroupName]

    ,case when Hlevel = 5 then GroupName else null end [Tier4!5!T4GroupName]

    ,case when Hlevel = 5 then GroupName else null end [Tier5!6!T5GroupName]

    from rcte r

    inner join #GroupList g on r.id = g.groupid

    order by rootid, tier1ID, tier2ID, tier3ID, tier4ID, tier5ID

    for xml explicit

    Results with

    <RootLevel GroupName="Parent1">

    <Tier1 T1GroupName="Child1">

    <Tier2 T2GroupName="SubChild1">

    <Tier3 T3GroupName="Icing" />

    </Tier2>

    <Tier2 T2GroupName="Subchild2">

    <Tier3 T3GroupName="Icing" />

    </Tier2>

    </Tier1>

    <Tier1 T1GroupName="Child2">

    <Tier2 T2GroupName="Subchild3" />

    </Tier1>

    </RootLevel>

    <RootLevel GroupName="Parent2">

    <Tier1 T1GroupName="Child1">

    <Tier2 T2GroupName="SubChild1">

    <Tier3 T3GroupName="Icing" />

    </Tier2>

    <Tier2 T2GroupName="Subchild2">

    <Tier3 T3GroupName="Icing" />

    </Tier2>

    </Tier1>

    <Tier1 T1GroupName="Child3" />

    </RootLevel>

  • mickyT (3/3/2013)


    Hi Craig

    I've had a go with your example data using the following and it appears to do what you require ... well the removing of empty tiers

    I hope it works for you and I haven't messed up your requirement

    Heya Micky, thanks for posting and helping out. Nope, you've got #3 there licked, now I just need to figure out why explicit doesn't expose dead/NULL tier levels but auto does. Thanks, one more tool to help me nail this down.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • if you use FOR XML RAW, you can also use the ELEMENTS and XSINIL clauses which should expose those other levels that you're looking for.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Here's one approach. I adapted some code from another XML generation solution, and it might be a little messy. One difference is that here you have more of a many-to-many relationship, so nodes are duplicated where they have multiple parents.

    Recursion is handled by a function which generates child nodes.

    -- This SQL script drops and creates objects: only use in an empty test/development database.

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[RecursiveGroupFinding]') AND type in (N'U'))

    DROP TABLE [RecursiveGroupFinding]

    GO

    CREATE TABLE RecursiveGroupFinding

    (ParentIDINTNULL,

    ChildIDINTNOT NULL

    )

    GO

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[GroupList]') AND type in (N'U'))

    DROP TABLE [dbo].[GroupList]

    GO

    CREATE TABLE GroupList

    (GroupIDINTIDENTITY(1,1) NOT NULL,

    GroupNameVARCHAR(30) NOT NULL

    )

    GO

    CREATE CLUSTERED INDEX idx_c_RecursiveGroupFinding ON RecursiveGroupFinding (ParentID, ChildID)

    CREATE CLUSTERED INDEX idx_c_GroupList ON GroupList (GroupID)

    INSERT INTO GroupList

    VALUES ('Parent1'),('Parent2'),('Child1'),('Child2'),('Child3'),('SubChild1'),('Subchild2'),('Subchild3'),('Icing')

    INSERT INTO RecursiveGroupFinding

    VALUES (NULL, 1), (NULL, 2), ( 1, 3), (1, 4), (2, 3),(2,5), (3, 6), (3, 7), (4, 8), (6, 9), (7,9)

    GO

    -- Function to create child nodes; uses recursion and a stopper.

    --CREATE FUNCTION dbo.NodeBuilder2

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[NodeBuilder2]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

    DROP FUNCTION NodeBuilder2

    GO

    CREATE FUNCTION NodeBuilder2

    (@BelongsTo SMALLINT)

    RETURNS XML

    AS

    BEGIN

    DECLARE @Node XML

    SET @Node =

    (SELECT g2.GroupName AS '@Title', gf.ChildID AS '@ID', gf.ParentID AS '@ParentID',

    CASE

    WHEN (SELECT COUNT(*) FROM RecursiveGroupFinding AS P2 WHERE P2.ChildID = gf.ParentID) > 0

    THEN (SELECT dbo.NodeBuilder2(gf.ChildID))

    ELSE NULL

    END

    FROM RecursiveGroupFinding AS gf

    JOIN

    GroupList AS g

    ON gf.ParentID = g.GroupID

    JOIN

    GroupList AS g2

    ON gf.ChildID = g2.GroupID

    WHERE gf.ParentID = @BelongsTo

    FOR XML PATH('Group'), TYPE)

    RETURN @Node

    END

    ;

    GO

    -- XML builder, uses function to create child nodes.

    WITH GroupParentChild

    AS (

    SELECT gf.ChildID, g2.GroupName, gf.ParentID

    FROM RecursiveGroupFinding AS gf

    LEFT OUTER JOIN

    GroupList AS g

    ON gf.ParentID = g.GroupID

    JOIN

    GroupList AS g2

    ON gf.ChildID = g2.GroupID

    )

    SELECT

    P0.GroupName AS '@Title', P0.ChildID AS '@ID', P0.ParentID AS '@ParentID',

    (SELECT dbo.NodeBuilder2(P0.ChildID))

    FROM GroupParentChild AS P0

    WHERE P0.ParentID IS NULL

    FOR XML PATH('Group'), TYPE, ROOT('Root')

    I changed some of the code so that a group could have NULL parents, and since that could result in multiple top-parent groups, I put a Root element at the top.

    The output should look like:

    <Root>

    <Group Title="Parent1" ID="1">

    <Group Title="Child1" ID="3" ParentID="1">

    <Group Title="SubChild1" ID="6" ParentID="3">

    <Group Title="Icing" ID="9" ParentID="6" />

    </Group>

    <Group Title="Subchild2" ID="7" ParentID="3">

    <Group Title="Icing" ID="9" ParentID="7" />

    </Group>

    </Group>

    <Group Title="Child2" ID="4" ParentID="1">

    <Group Title="Subchild3" ID="8" ParentID="4" />

    </Group>

    </Group>

    <Group Title="Parent2" ID="2">

    <Group Title="Child1" ID="3" ParentID="2">

    <Group Title="SubChild1" ID="6" ParentID="3">

    <Group Title="Icing" ID="9" ParentID="6" />

    </Group>

    <Group Title="Subchild2" ID="7" ParentID="3">

    <Group Title="Icing" ID="9" ParentID="7" />

    </Group>

    </Group>

    <Group Title="Child3" ID="5" ParentID="2" />

    </Group>

    </Root>

  • Tavis,

    That looks awesome, and similar to where I was probably going to end up with self-referencing proc calls but this looks a lot cleaner.

    Thank you for this. Once I get myself out of a rolling fireball at work I'll be able to test this out and modify it to my needs.

    Thanks again.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • You are welcome, Evil Kraig F 🙂

    I did notice I made a mistake in naming the ID attribute, since XML ID attributes are specially reserved and should have a value unique in the document. I would change the name of that attribute to "identifier" or something, not "ID".

  • Not sure if you're still on this thread, Tavis, but that's a neat trick. May require some on the fly trickery to my base data to NULL, child the parents, but I can arrange that.

    I like how the XML becomes like entity information in the outer XML wrappers. It avoids string manipulation to bury groups within groups.

    However, this is a perfect start to what I need. Thanks again. Now I just need to go through it and modify it to work with the schema I actually have instead of the sample build I provided here.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

Viewing 10 posts - 1 through 9 (of 9 total)

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