Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

FOR XML and trying to avoid nested cursors Expand / Collapse
Author
Message
Posted Sunday, March 3, 2013 1:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:27 AM
Points: 6,253, Visits: 7,429
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
Post #1425920
Posted Sunday, March 3, 2013 12:01 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 4,427, Visits: 3,423
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.
Post #1425987
Posted Sunday, March 3, 2013 1:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:27 AM
Points: 6,253, Visits: 7,429
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1425999
Posted Sunday, March 3, 2013 1:34 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:57 PM
Points: 1,020, Visits: 3,065
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>

Post #1426004
Posted Sunday, March 3, 2013 2:28 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:27 AM
Points: 6,253, Visits: 7,429
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
Post #1426017
Posted Monday, March 4, 2013 9:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, September 13, 2014 8:51 AM
Points: 6,600, Visits: 8,900
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
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
Post #1426309
Posted Wednesday, March 6, 2013 3:36 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:54 AM
Points: 25, Visits: 125
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>

Post #1427269
Posted Wednesday, March 6, 2013 12:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:27 AM
Points: 6,253, Visits: 7,429
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
Post #1427586
Posted Wednesday, March 6, 2013 2:52 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:54 AM
Points: 25, Visits: 125
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".

Post #1427648
Posted Tuesday, March 12, 2013 4:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:27 AM
Points: 6,253, Visits: 7,429
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
Post #1430144
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse