SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


FOR XML and trying to avoid nested cursors


FOR XML and trying to avoid nested cursors

Author
Message
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21009 Visits: 7660
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
(ParentID INT NOT NULL,
ChildID INT NOT NULL
)

CREATE TABLE #GroupList
(GroupID INT IDENTITY(1,1) NOT NULL,
GroupName VARCHAR(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
ON gf.ParentID = g.GroupID
JOIN
#GroupList AS g2
ON gf.ChildID = g2.GroupID




Parent1 Child1
Parent1 Child2
Parent2 Child1
Parent2 Child3
Child1 SubChild1
Child1 Subchild2
Child2 Subchild3
SubChild1 Icing
Subchild2 Icing


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
ON g.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
ON g.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
ON g.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
ON g.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
ON g.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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Revenant
Revenant
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12609 Visits: 5010
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.
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21009 Visits: 7660
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. :-D


- 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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
mickyT
mickyT
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2878 Visits: 3318
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>


Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21009 Visits: 7660
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
WayneS
WayneS
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21928 Visits: 10653
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Tavis Reddick
Tavis Reddick
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 Visits: 191
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
(ParentID INT NULL,
ChildID INT NOT 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
(GroupID INT IDENTITY(1,1) NOT NULL,
GroupName VARCHAR(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>


Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21009 Visits: 7660
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Tavis Reddick
Tavis Reddick
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 Visits: 191
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".
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21009 Visits: 7660
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search