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