"order by" in xquery

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!!

% Wildcard Include Nulls

I have a query with 4 parameters:

Name

Location

Employee Number

Officer Code

There are no null values in Name, location and Employee Number. However, all employees do not have an officer code. So in my query I use a where clause that says WHERE OfficerCode LIKE'%'+@.Param4+'%'. I use a % for the default value in my ASP.net data control. The only problem is that the only records returned have non-null values in the OfficerCode field. How can I use a wildcard for a default value and return all records (null and non-null)?

OR OfficerCode IS NULL