XQuery for the SQL programmer – Publishing data as XML

Publishing data as XML... What did you expect, of course that such topic needs to be handled in a series like XQuery for the SQL Programmer. Despite the enormous usage of XML throughout most application development initiatives these days, publishing relation data as XML using SQL dialects or extensions is still cumbersome through. Cumbersome meaning that first this is basically handled through a different language, embedded in your SQL statements. And second, there is still no cross database solution to handle this, there are differences between various database flavors, frequently even between database versions.

Let’s start with a simple use case, and generate an XML structure as follows, based on the USERS table,

[cc lang="xquery"]

Tom Jones B

Mary Doe A

... [/cc]>

In SQL Server, using FOR XML PATH,

[cc lang="sql"]SELECT USERID as '@id', NAME as name, RATING as rating FROM USERS FOR XML PATH ('user'), ROOT('users')[/cc]

Or if your database supports the SQL/XML standard, like Oracle 10gR2 or DB2 v9, you can use the SQL/XML publishing functions. Here is the Oracle 10gR2 variant, note that some of the details will need to be changed to make it work on DB2.

[cc lang="xquery"]SELECT xmlelement(name "users", (SELECT xmlagg( xmlelement(name "user", xmlattributes(USERID as id), xmlelement(name "name", NAME), xmlelement(name "rating", RATING) ) ) FROM USERS) ) FROM DUAL[/cc]

In XQuery we could write the following. Note the elegance of the solution as the generated XML is readable within the query,

[cc lang="xquery"]{ for $u in collection("USERS")/USERS return

{$u/NAME/text()} {$u/RATING/text()}

}[/cc]

Let’s now extend this existing XML, and include for every user the bids being placed. Imagine we want the following kind of result,

[cc lang="xquery"]

Tom Jones B

400 1999-02-14

40 1999-03-05

... [/cc]

Unfortunately, such XML structure cannot be generated through SQL Server's FOR XML PATH; we need to use FOR XML EXPLICIT. If you’re familiar with it, I’m sure you appreciate some of the details of the FOR XML PATH construct in SQL Server. Let me give it a try,

[cc lang="sql"]SELECT Tag, Parent, [users!1!], [user!2!id], [user!2!name!Element], [user!2!rating!Element], [bids!3!], [bid!4!id], [bid!4!bid!Element], [bid!4!date!Element] FROM ( SELECT 1 AS Tag, NULL AS Parent, 0 AS Sort, NULL AS 'users!1!', NULL AS 'user!2!id', NULL AS 'user!2!name!Element', NULL AS 'user!2!rating!Element', NULL AS 'bids!3!', NULL AS 'bid!4!id', NULL AS 'bid!4!bid!Element', NULL AS 'bid!4!date!Element' UNION ALL SELECT 2 AS Tag, 1 AS Parent, convert(integer, substring(USERID, 2,2)) * 100 AS Sort, NULL, USERID, NAME, RATING, NULL, NULL, NULL, NULL FROM USERS UNION ALL SELECT 3 AS Tag, 2 AS Parent, convert(integer, substring(USERID, 2,2)) * 100 + 1 AS Sort, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL FROM USERS UNION ALL SELECT 4 AS Tag, 3 AS Parent, convert(integer, substring(USERID, 2,2)) * 100 + 2 AS Sort, NULL, NULL, NULL, NULL,NULL, USERID, BID, BID_DATE FROM BIDS ) A ORDER BY Sort FOR XML EXPLICIT[/cc]

And if you have a SQL/XML background, here is an Oracle 10gR2 compatible query,

[cc lang="xquery"]SELECT xmlelement(name "users", (SELECT xmlagg( xmlelement(name "user", xmlattributes(u.USERID as id), xmlelement(name "name", u.NAME), xmlelement(name "rating", u.RATING), xmlelement(name "bids", (SELECT xmlagg( xmlelement(name "bid", xmlattributes(b.ITEMNO as id), xmlelement(name "bid", b.BID), xmlelement(name "date", b.BID_DATE) ) ) FROM BIDS b WHERE b.USERID = u.USERID) ) ) ) FROM USERS u) ) FROM DUAL[/cc]

The XQuery version is more concise, more readable and as a consequence more maintainable,

[cc lang="xquery"]{ for $u in collection("USERS")/USERS return

{$u/NAME/text()} {$u/RATING/text()} { for $b in collection("BIDS")/BIDS where $b/USERID = $u/USERID return

{$b/BID/text()} {$b/BID_DATE/text()}

}

}[/cc]

There is a subtle difference between the above SQL and XQuery queries. In SQL, be it the SQL/XML standard or Microsoft SQL Server’s FOR XML approach, element construction is skipped in case of NULL data. This is not the case with the XQuery queries. Consider the following query from above,

[cc lang="xquery"]{ for $u in collection("USERS")/USERS return

{$u/NAME/text()} {$u/RATING/text()}

}[/cc]

If for some record in our database, the NAME or RATING are NULL, the or element would still be included the query result. In order to make the XQuery fully compatible with SQL, the following trick can be used. Up to now we have been adding the path expression to select the data, as enclosed expressions inside the element constructors,

[cc lang="xquery"]{$u/RATING/text()}[/cc]

If we simply select the data, and add the element constructor as last step in the path expression, we get the desired effect.

[cc lang="xquery"]$u/RATING/{./text()}[/cc]

If for some user the rating is NULL, then $u/RATING will evaluate to the empty sequence, as a consequence the element will not be created.
Applying this to our complete query we have the following,

[cc lang="xquery"]{ for $u in collection("USERS")/USERS return { $u/USERID/attribute id {.}, $u/NAME/{./text()}, $u/RATING/{./text()} } }[/cc]

I hope this post gave a sense on the power and simplicity, when it comes to publishing relational data as XQuery.
All posts in the XQuery for the SQL Programmer series have been about querying your relational databases. Next we’ll discuss updates. Can you update your data through XQuery, and how does this compare to what you are used to in SQL?