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,

<users>
  <user id="U01">
    <name>Tom Jones</name>
    <rating>B</rating>
  </user>
  <user id="U02">
    <name>Mary Doe</name>
    <rating>A</rating>
  </user>
  ...
</users>

In SQL Server, using FOR XML PATH,

SELECT
    USERID as '@id',
    NAME as name,
    RATING as rating
FROM USERS FOR XML PATH ('user'), ROOT('users')

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.

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

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

<users>{
  for $u in collection("USERS")/USERS
  return
    <user id="{$u/USERID}">
      <name>{$u/NAME/text()}</name>
      <rating>{$u/RATING/text()}</rating>
    </user>
}</users>

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

<users>
  <user id="U01">
    <name>Tom Jones</name>
    <rating>B</rating>
    <bids>
      <bid id="1002">
        <bid>400</bid>
        <date>1999-02-14</date>
      </bid>
      <bid id="1004">
        <bid>40</bid>
        <date>1999-03-05</date>
      </bid>
    </bids>
  </user>
  <user id="U02">
  ...
</users>

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,

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

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

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

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

<users>{
  for $u in collection("USERS")/USERS
  return
    <user id="{$u/USERID}">
      <name>{$u/NAME/text()}</name>
      <rating>{$u/RATING/text()}</rating>
      <bids>{
        for $b in collection("BIDS")/BIDS
        where $b/USERID = $u/USERID
        return
          <bid id="{$b/ITEMNO}">
            <bid>{$b/BID/text()}</bid>
            <date>{$b/BID_DATE/text()}</date>
          </bid>
      }</bids>
    </user>
}</users>

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,

<users>{
  for $u in collection("USERS")/USERS
  return
    <user id="{$u/USERID}">
      <name>{$u/NAME/text()}</name>
      <rating>{$u/RATING/text()}</rating>
    </user>
}</users>

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,

<rating>{$u/RATING/text()}</rating>

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

$u/RATING/<rating>{./text()}</rating>

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,

<users>{
  for $u in collection("USERS")/USERS
  return
    <user>{
      $u/USERID/attribute id {.},
      $u/NAME/<name>{./text()}</name>,
      $u/RATING/<rating>{./text()}</rating>
    }</user>
}</users>

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?

Tech Tags: XQuery SQL