Native XML Database

How to order query results based on XML values…. this seems like a simple question, doesn’t it? It’s indeed not hard, but there are a few useful things to know about it.

Assume we have a set of XML documents that describe “employee” information, and we want to return the employee names (/emp/name) ordered by the employees’ ID numbers (/emp/@id).

This is very easy to write in XQuery:

for $i in db2-fn:xmlcolumn("EMPLOYEE.DOC")/emp
order by $i/@id
return $i/name/text()

In this example the employee documents are stored in the XML column “DOC” in the table “EMPLOYEE”. The order by clause reorders the sequence of “emp” elements based on the @id attribute, and that’s the order in which the names. At the end of the order by clause you can add the keyword ascending or descending to choose a sort order.

Now, let’s assume we are querying a mix of XML and relational data, which is a common requirement, and want to do the same ordering in a SQL/XML query. As a first (but incorrect) attempt we might write the following query:

SELECT XMLQUERY('$i/emp/name/text()' PASSING doc AS "i")
FROM employee
ORDER BY XMLQUERY('$i/emp/@id' PASSING doc AS "i");

SQL20353N An operation involving comparison cannot use operand
"Ordering column 1" defined as data type "XML". SQLSTATE=42818

The error message SQL20353N produced by DB2 tells us that we cannot order on a column of type XML. In this example, the XML column that we try sort by is produced by the XMLQUERY function in the SQL order by clause. The XMLQUERY function always produces values of data type XML.

The SQL/XML standard defines that values of type XML cannot be compared to determine whether one is greater than the other. This restriction makes sense, because an individual value of type XML can be a full document, a document fragment, a sequences of multiple values, or a single atomic value. In SQL/XML there is no well-defined way to decide, for example, whether one XML document is greater than another, or whether an XML document is greater than 5.

Since comparison is at the heart of ordering, ordering on XML values in an SQL order by clause is not possible – UNLESS you cast the XML values to a data type that SQL can reasonably compare and order. The following query converts the extracted @id attribute to INTEGER, which allows the sorting as you would expect:

SELECT XMLQUERY('$i/emp/name/text()' PASSING doc AS "i")
FROM employee
ORDER BY XMLCAST( XMLQUERY('$i/emp/@id' PASSING doc AS "i") as INTEGER);

Another option is to sort on a column produced by the XMLTABLE function:

SELECT empid, empname
FROM employee,
     XMLTABLE('$i/emp' PASSING doc AS "i"
        COLUMNS
           empname VARCHAR(25) PATH 'name',
           empid   INTEGER     PATH '@id')
ORDER BY empid ;

Happy sorting!