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, 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,
Applying this to our complete query we have the following,
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?
<users>In SQL Server, using FOR XML PATH,
<user id="U01">
<name>Tom Jones</name>
<rating>B</rating>
</user>
<user id="U02">
<name>Mary Doe</name>
<rating>A</rating>
</user>
...
</users>
SELECTOr 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.
USERID as '@id',
NAME as name,
RATING as rating
FROM USERS FOR XML PATH ('user'), ROOT('users')
SELECTIn XQuery we could write the following. Note the elegance of the solution as the generated XML is readable within the query,
xmlelement(name "users",
(SELECT
xmlagg(
xmlelement(name "user",
xmlattributes(USERID as id),
xmlelement(name "name", NAME),
xmlelement(name "rating", RATING)
)
)
FROM USERS)
)
FROM DUAL
<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>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,
<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>
SELECTAnd if you have a SQL/XML background, here is an Oracle 10gR2 compatible query,
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
SELECTThe XQuery version is more concise, more readable and as a consequence more maintainable,
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
<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 <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 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?
posted by marc at
11:39 AM
|
0 Comments
