Learn XQuery

Sorting Data

DataDirect XQuery® leverages the database for sorting, because the database can leverage indexes to sort much more efficiently.

Example 5. Order By Pushdown

The XQuery uses the Order By clause to sort database data in the database.

XQuery

for $u in collection('USERS')/USERS
order by $u/LASTNAME
return <user name="{$u/LASTNAME}"/>

Generated SQL

SELECT ALL
  nrm5."LASTNAME" AS RACOL1
FROM
  "PEPPINO"."USERS" nrm5
ORDER BY
  nrm5."LASTNAME" ASC

Example 6. Order By Pushdown - empty least

DataDirect XQuery® supports all variants of Order By in XQuery, pushing them down to the database. For instance, here is the SQL generated for a query that uses the empty least clause.

XQuery

for $u in collection('USERS')/USERS
order by $u/OTHERNAME descending empty least
return <user name="{$u/LASTNAME}"/>

Generated SQL (Oracle 10gR2)

SELECT ALL
  nrm5."LASTNAME" AS RACOL1
FROM
  "PEPPINO"."USERS" nrm5
ORDER BY
  nrm5."OTHERNAME" DESC NULLS LAST

Example 7. Sorting empty greatest on Microsoft SQL Server

In some cases, supporting an XQuery sort order requires some ingenuity. For instance, Microsoft SQL Server does not support sorting NULL high, so we use a simple trick to implement empty greatest for this database efficiently, while still correctly implementing the semantics of XQuery.

XQuery

for $u in collection('USERS')/USERS
order by $u/OTHERNAME descending empty greatest
return <user name="{$u/LASTNAME}"/>

Generated SQL (SQL Server)

SELECT ALL
  nrm5."LASTNAME" AS RACOL1
FROM
  "PEPPINO"."USERS" nrm5
ORDER BY
  (CASE WHEN nrm5."OTHERNAME" IS NULL THEN 0 ELSE 1 END) ASC,
  nrm5."OTHERNAME" DESC

Next: "Building XML Hierarchies" Prev: "Joins"

Try DataDirect XQuery® Free!

Put the power, scalability, and performance of DataDirect XQuery® to work for you today! Our free trial lets you see for yourself how easy it is to build data integration applications that access relational, EDI, and other file formats as XML!

Download DataDirect XQuery® today!

Online Video Tutorials!

Our easy-to-follow online video tutorials are a great way to get acquainted with the many features of DataDirect XQuery®.

And if you like what you see, download a free copy today and try DataDirect XQuery® for yourself!

Back to School with DataDirect XQuery®

Getting your mind around the possibilities of a data integration technology as promising as XQuery can be difficult, but our XML developers Webinars will help you understand the power and versatility of XQuery, and our favorite XQuery processor, DataDirect XQuery®.

Stay Informed!

XQuery is one of the hottest XML technologies being developed today. Stay informed with vital news about standards, tools, and trends by signing up for the DataDirect XQuery® newsletter.

Feeds

Multimedia

Newsletter