How to order query results based on XML values

August 11, 2010

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!

About these ads

3 Responses to “How to order query results based on XML values”

  1. Kim Breitwieser Says:

    Is there a way to do this in Hibernate? I already wrote my own Order-Bean (copied it from here: http://blog.tremend.ro/2008/06/10/how-to-order-by-a-custom-sql-formulaexpression-when-using-hibernate-criteria-api/) but i always get he same error:

    DB2 SQL Error: SQLCODE=-214, SQLSTATE=42822, SQLERRMC=XMLQUERY/XMLEXISTS;ORDER BY;6, DRIVER=3.61.75

    It works everywhere except for Hibernate.

    The generated query:

    select this_.”IDN” as y0_ from “EAI”.”MonitoringRequest” this_ order by XMLCAST(XMLQUERY(‘$d/EAI/Karte/KundenNummer’ PASSING this_.”MessageBody” AS “d”) AS INTEGER) asc


    • Hi Kim, the generated query looks correct. I just tried it (without Hibernate) and as you say, it works just fine. I don’t know Hibernate deep enough to understand why the same query would receive SQL error 214 through Hibernate.

      The other option for sorting by an XML value is to use the XMLTABLE function, as described in this blog post. Does that work for you through Hibernate?

      – Matthias

      • Kim Breitwieser Says:

        Using XMLTABLE could be a bit problematic as i have to declare the used tables in my entity beans like this:

        @Table(name = “MONITORINGREQUEST”, schema = “EAI”)
        public class MonitoringRequestImpl implements MonitoringRequestI, Serializable { … }

        It seems like the error i am getting is specific to DB2 v9.5 where as we are currently using v9.7. Both SQuirreL (where it works) and Hibernate (where it doesn’t work) are using the same driver so i guess it has to be a problem with the Hibernate version we are using (3.0).


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 51 other followers

%d bloggers like this: