troubleshooting Question

Recursive XML nodes query in SQL Server

Avatar of Declan Basile
Declan BasileFlag for United States of America asked on
XMLMicrosoft SQL Server
3 Comments1 Solution12 ViewsLast Modified:
Using SQL Server 2012, I need to convert XML to a string such that, for each <PId> element at any level, the Element names of all the parent elements are concatenated in order (highest to lowest level nodes), delimited by a ".", and the value of the <PId> element is then concatenated to that string.  

For example, this XML:
'<P><PId>165</PId><PId>170</PId><P98><PId>5</PId></P98><P99><P100><PId>5</PId></P100></P99></P>'
Becomes 4 records for the 4 <PId> elements:
P.165
P.170
P.P98.5
P.P99.P100.5

If I use something like
SELECT T.C.query('..')
FROM @XML.nodes('//PId') T(C)
in a CTE recursive statement, I believe query('..') gives the parent element, not a copy of the original XML with the context node set to the parent element, so I loose visibility of the parent's parent element.

How can I accomplish this?  Note: It's a given that the highest level node with always be <P>, and that every node between <P> and <PId> with be <P + some number>.

ASKER CERTIFIED SOLUTION
ste5an
Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros