Avatar of Declan Basile
Declan Basile
Flag for United States of America asked on

Recursive XML nodes query in SQL Server

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:
Becomes 4 records for the 4 <PId> elements:

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>.

XMLMicrosoft SQL Server

Avatar of undefined
Last Comment
Declan Basile

8/22/2022 - Mon

You mean you want to convert a XML structure saved in a text type field into a readable format?
Cast(textfield as XML)

or you have a XML datetype and want covert this?
Cast(xmlfield as nvarchar(xx)) or any other text type...

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Declan Basile

Thanks.  I ended up doing something similar to what you posted.  The key idea that I thought of was to write code that exhaustively creates a record for each element including all it's parent elements and then filtering out the ones that don't end in "<PId>".  Instead of starting with the child elements and traversing upwards I started with the parent elements and traversed downward, filtering out unwanted records in the end.  This is what it looks like (note: I added a few more things than mentioned in my original question.  I wanted to keep the question as simple as possible.)

   WITH XYZ (X, Y)



      SELECT T.C.query('.') As X, 

      CONVERT(varchar(MAX), T.C.query('if (fn:local-name(.) = "PId") then (data(.)) else fn:local-name(.)')) As Y

      FROM @XML_R.nodes('/P/*') T(C)


      SELECT T.C.query('.') As X, 

      Z.Y + '.' + CONVERT(varchar(MAX), T.C.query('if (fn:local-name(.) = "PId") then (data(.)) else fn:local-name(.)')) As Y

      FROM XYZ Z CROSS APPLY X.nodes('./*/*') T(C)


   SELECT @RPFields = STUFF((SELECT ', P' + REPLACE(Y, 'P', '') FROM XYZ WHERE RIGHT(CONVERT(varchar(MAX), X), 6) = '</PId>' FOR XML PATH('')), 1, 2, '')

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck