2

Is the result of a SELECT like the one below using XQuery in SQL Server guaranteed to be in document order, that is in the original order the nodes are listed in the xml string?

DECLARE @x XML = '<hey i="3"/><hey i="4"/><hey i="0"/>'
SELECT t.i.value('.', 'int')
FROM @x.nodes('/hey/@i') t(i)

I ask because in general SELECT statements do not guarantee an order unless one is provided.

Also, if this order is (or is not) guaranteed, is that documented somewhere officially, maybe on Microsoft's web site?

Last, is it possible to sort opposite of document order or do other strange sorts and queries based on the original document order from within the SELECT statement?

user12861
  • 2,358
  • 4
  • 23
  • 41

4 Answers4

2

The DECLARE @x XML = '<hey i="3"/><hey i="4"/><hey i="0"/>' is an example of an XML "sequence". By definition, without a sort order, the select should always come back in the documents original order.

As already mentioned, you can change the sort order. Here is one example:

SELECT t.i.value('.', 'int') as x, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rn

FROM @x.nodes('/hey/@i') t(i)

order by rn desc

Here is some information about sequences on the Microsoft site:

http://msdn.microsoft.com/en-us/library/ms179215(v=sql.105).aspx

Here is a more general discussion of a sequence in Xquery.

http://en.wikibooks.org/wiki/XQuery/Sequences

I realize that my original answer above is incorrect after reading the page on the Microsoft site I referred to above. That page says that you need a comma between elements to construct a sequence. The example given is not a "sequence". However, my original info about changing the sort order stands :)

Michael Harmon
  • 746
  • 5
  • 10
1

I think that same rules of Select apply here, no matter if you're selectig from ordinary table, or XML. There's selection part, and there's projection part, and the engine can take different paths to retrieve your data (from the middle sideways, for example). Unfortunately I can't find any official document to support that. And for sure, there's no intrinsic table/document order that you can access and manipulate.

AdamL
  • 12,421
  • 5
  • 50
  • 74
  • The documentation found by @dferidarov in the comment above appears to contradict this, though it's not completely clear to me. – user12861 Apr 11 '13 at 14:03
1

You can add order by clause to the select statement.

DECLARE @x XML = '<hey i="3"/><hey i="4"/><hey i="0"/>'SELECT [column1] = t.i.value('.', 'int') FROM @x.nodes('/hey/@i') t(i)  order by column1 desc
dferidarov
  • 602
  • 4
  • 15
  • That's not the order I'm interested in. That's ordered by a value stored in the xml, which I already know how to do. I'm interested in the original order the nodes were listed in the string. And maybe reversing that order and doing other stuff with it too. – user12861 Apr 05 '13 at 14:53
  • Did you see my example of sorting above? – Michael Harmon Apr 05 '13 at 15:05
  • I did see it, and I'm thinking about it. – user12861 Apr 06 '13 at 15:45
1

I know what you mean about this. I suspect the order would be document order, but the documentation does not make it clear, and relying on it implicitly just isn't nice.

One way to be confident about the order would be:

DECLARE @x XML = '<hey i="3"/><hey i="4"/><hey i="0"/>';
select @x.value('(/hey[sql:column("id")]/@i)[1]', 'int'), id
from (
    select row_number() over (order by (select 0)) as id
    from @x.nodes('/hey') t(i)
) t
order by id

This would then give you a way to answer your other question, i.e. getting the values in reverse, or some other, order.

N.B. This is going to be much slower than just using nodes as the size of your XML increases.

muhmud
  • 4,474
  • 2
  • 15
  • 22