Uniquely Identifying XML Nodes with DENSE_RANK

re: Uniquely Identifying XML Nodes with DENSE_RANK

Alejandro Mesa — Wed, 05 Aug 2009 01:05:47 GMT

Adam,

Clever solution, indeed.

I can see in the execution plan, that the segmentation and sequence is done using an id returned by the xml reader.

Here is another way to accomplish this, but is not as good as the one from Mladen. It uses the all time favorite auxiliary table of numbers, and also relies on counting the parent nodes.

declare @Numbers table (

Number int not null unique

);

insert into

@Numbers(Number)

Values

(1),

(2),

(3),

(4),

(5);

declare @x xml;

set @x = '<a><b><c>abc</c><c>def</c></b><b><c>abc</c><c>def</c></b></a>';

select

T.c.value('(./text())[1]', 'varchar(25)') as c,

N.Number

from

@Numbers as N

cross apply

@x.nodes('/a/b[position() = sql:column("N.Number")]/.') as X(n)

cross apply

X.n.nodes('./c') as T(c)

where

N.Number <= @x.query('count(/a/b)').value('.', 'int');

All this could be avoided if we were able to have access to the position or even the id of the node. BTW, here is connect entry in case you feel like it deserves one more vote.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=383888&wa=wsignin1.0

Cheers,

AMB

re: Uniquely Identifying XML Nodes with DENSE_RANK

Adam Machanic — Wed, 05 Aug 2009 02:54:31 GMT

Hi Alejandro,

Voted!

re: Uniquely Identifying XML Nodes with DENSE_RANK

Matthew Darwin — Wed, 29 Dec 2010 16:54:13 GMT

Thanks very much for this, an excellent solution that's really helped massively.

re: Uniquely Identifying XML Nodes with DENSE_RANK

Maciej Raszplewicz — Tue, 22 Mar 2011 13:58:48 GMT

Adam,

Is result order of this query with dense_rank always the same? I know that select is non-deterministic but what if I select data from xml?

Maciek

re: Uniquely Identifying XML Nodes with DENSE_RANK

Adam Machanic — Tue, 22 Mar 2011 15:14:59 GMT

Maciej,

No, order is never guaranteed unless you use ORDER BY -- XML or no. Please use ORDER BY :-)

re: Uniquely Identifying XML Nodes with DENSE_RANK

Maciej Raszplewicz — Tue, 22 Mar 2011 15:24:46 GMT

I mean order of dense_rank. There is ORDER BY b_node there.

My first question was not exactly what I meant.

re: Uniquely Identifying XML Nodes with DENSE_RANK

Serg Yurevich — Thu, 28 Mar 2013 16:29:13 GMT

This is very elegant solution. I like it very much and it helped me much. Genius!