When working with XML in SQL Server, you might want to uniquely identify one node against another. But due to the flexibility with which XML can be defined, this is not always directly possible. SQL Server's own XML structures are guilty of having this problem, as I discovered while writing the Extended Events Code Generator. Events in the XML produced by XE have a limited number of attributes and depending on what you're collecting you may get numerous instances of events that look exactly the same. I wanted to provide a way to uniquely identify every collected event in order to allow for more advanced analysis of the results.
For the sake of this post I'll keep things a bit simpler than the Extended Events XML; we'll instead play with the following XML fragment:
<a>
<b>
<c>abc</c>
<c>def</c>
</b>
<b>
<c>abc</c>
<c>def</c>
</b>
</a>
The problem: We e want to pull all of the "c" values and, at the same time, tag whether they've come from the first or second "b" value. There are no attributes that we can use to uniquely identify the individual nodes, so we're rather stuck.
My first idea for solving this was to use ROW_NUMBER, but what to number? I came up with something similar to the following:
DECLARE @x XML
SET @x = '<a><b><c>abc</c><c>def</c></b><b><c>abc</c><c>def</c></b></a>'
SELECT
ROW_NUMBER() OVER (ORDER BY b_xml) AS unique_b_node,
c_node.value('(./text())[1]', 'varchar(50)') AS c_val
FROM
(
SELECT
b_node.query('.') AS b_xml
FROM @x.nodes('/a/b') AS b (b_node)
) b_nodes
CROSS APPLY b_nodes.b_xml.nodes('/b/c') AS c (c_node)
Alas, this approach met with utter failure; as you will discover if you actually try to run this code, you cannot sort on instances of the XML data type, and therefore you cannot use ROW_NUMBER to uniquely identify them. I thought I was finished, so I asked around on Twitter. Peter Debetta replied with an interesting XQuery method for solving the problem:
DECLARE @x XML
SET @x = '<a><b><c>abc</c><c>def</c></b><b><c>abc</c><c>def</c></b></a>'
SELECT
b_nodes.unique_b_node,
c_node.value('(./text())[1]', 'varchar(50)') AS c_val
FROM
(
SELECT
b_node.query('.') AS b_xml,
b_node.value('for $s in . return count(../*[. << $s]) + 1', 'int') AS unique_b_node
FROM @x.nodes('/a/b') AS b (b_node)
) b_nodes
CROSS APPLY b_nodes.b_xml.nodes('/b/c') AS c (c_node)
This actually works pretty well when your query only returns four rows, or even forty. But it breaks down quickly after that, due to the exponential nature of how the XQuery counts the rows, and at only a few hundred rows I found myself waiting a number of minutes for my results.
Later, Mladen Prajdic decided to jump in and try to help, and he came up with the key to solving the problem: While you can't order on XML instances, you can order on nodes instances resulting from a call to the nodes() function, as shown in the code below. Using ROW_NUMBER in this case would not yield the expected results; since there are four "c" nodes in the output, we would end up with four row numbers. DENSE_RANK, on the other hand, counts ties equally, so all "b" nodes wind up with the same number. A seemingly simple solution, but not exactly intuitive, and it took three of us a surprising amount of work to come up with it.
DECLARE @x XML
SET @x = '<a><b><c>abc</c><c>def</c></b><b><c>abc</c><c>def</c></b></a>'
SELECT
c_node.value('(./text())[1]', 'varchar(50)') AS c_val,
DENSE_RANK() OVER (ORDER BY b_node) AS unique_b_node
FROM @x.nodes('/a/b') AS b (b_node)
CROSS APPLY b.b_node.nodes('./c') AS c (c_node)