Given the following XML data in SQL Server (where each Employee is a separate rowset), is there a way to order by ID using XQuery?
Row 1<Employee><ID>3</ID></Employee>
Rpw 2<Employee><ID>1</ID></Employee>
Row 3<Employee><ID>2</ID></Employee>
Ex:
SELECT Employee.query('
for $emp in /Employee
order by $emp/ID
')
If you want to order multiple rows containing xml data you need to use SQL order by. XQuery order by is scoped to only one XML instance.
Assuming that table t has the Employee column, you can try something like this:
SELECT Employee
FROM t
ORDER BY Employee.value('(/Employee/ID)[1]', 'int')
|||Adrian, you are the man! Thanks!!