SQL Server: Order by XML column's node

Question

I was wondering can I ORDER BY my query by node of an XML typed column?

For example I have a table

ID (int) | Data (XML)

Where Data column stores XML in form similar to this

<?xml?>
<Data>
   <SimpleOrderedValue>1</SimpleOrderedValue>
   <ComplicatedInternals>
      ...
   </ComplicatedInternals>
</Data>

I want query this table ordered by SimpleOrderedValue. Can I do this on MS SQL Server 2008 with XML column? Or can I do thins using CLR UDT, but without additional computed column and in a way so that Data column were indexed (for faster search).

Will appreciate any help. Thanks.

Alex K. · Accepted Answer · 2010-05-27 13:12:53Z

4

How about an XQuery expression;

select id, data
from T
  order by data.value('(/Data/SimpleOrderedValue)[1]', 'int')

answered May 27, 2010 at 13:12

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Insomniac Over a year ago

Thanks Alex, will it use indexes if I use this expression in where clause?

Alex K. Over a year ago

It should do as the XML is only parsed in the ORDER BY, so it should SELECT using an index then just sort the result, if you look at the query plan you will be able to see.

dyesdyes Over a year ago

YOu can create XML indexes: msdn.microsoft.com/en-us/library/ms191497.aspx

Collectives™ on Stack Overflow

SQL Server: Order by XML column's node

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related