In this second post of the XQuery for the SQL programmer series we’ll explain how to represent relational tables in XML and query them through XQuery.
SQL/XML mapping
The SQL/XML standard, specifies a mapping for relational tables into XML. Actually, SQL/XML includes a set of mapping rules. These mappings can be parameterized in several ways, including the target namespace for the XML structure, whether to handle nulls using xsi:nil or absence, and whether to map a table to a single element or a forest of elements.
This last parameter – is the table mapped to a forest or not? – is mostly affecting the XML structure. Here is the mapping for the USERS table with SQL/XML forest parameter set to false. As we see there is a single root element, and every row in our table becomes a element. Each element contains elements representing every single column.
[cc lang="xquery"]
U01 Tom Jones B
U02 Mary Doe A
... [/cc]
Setting the SQL/XML forest parameter to true, we get an XML structure as shown below. Every row in the USERS table is mapped to a element.
[cc lang="xquery"] U01 Tom Jones B
U02 Mary Doe A
...[/cc]
In case you are curious why this mapping option is named SQL/XML forest, not there is not a single root element in this scenario, this is what we call an XML forest and hence the name of this option.
In the end there isn’t much of a difference between both mapping approaches, one is not better than the other. It is more a matter of taste than anything else.
As the XML structure is different depending on the mapping, it will affect the way our queries look like. Consider a simple example, get the description of the item with itemno 1004,
[cc lang="sql"]select DESCRIPTION from ITEMS where ITEMNO = '1004'[/cc]
Assuming SQL/XML forest set to true this becomes,
[cc lang="xquery"]for $item in collection("ITEMS")/ITEMS where $item/ITEMNO = "1004" return $item/DESCRIPTION[/cc]
Set to false we have to write the query as follows, note the additional row step,
[cc lang="xquery"]for $item in collection("ITEMS")/ITEMS/row where $item/ITEMNO = "1004" return $item/DESCRIPTION[/cc]
DataDirect XQuery supports both mappings, the default for the SQL/XML forest parameter is true. All future examples and use cases in this series will assume this default.
Mapping NULL values
SQL NULL values are mapped as absent elements. Just like there is a difference between an empty string and NULL in SQL, there is a difference in XML between an empty element and an absent element. Suppose Tom Jones’ rating is NULL, and Mary Doe’s rating it the empty string. The XML representation of our USERS table would be as follows,
[cc lang="xquery"] U01 Tom Jones
U02 Mary Doe
...[/cc]
Retrieving all users which have a rating in SQL,
[cc lang="xquery"]select * from USERS where RATING IS NOT NULL[/cc]
We’ll explain it in more detail in a subsequent post, but the XQuery variant is as follows,
[cc lang="xquery"]for $user in collection("USERS")/USERS where $user/RATING return $user[/cc]
Another way to represent a NULL value is by using the XML Schema xsi:nil mechanism. The USERS table outlined above would be structured as follows,
[cc lang="xquery"] U01 Tom Jones
U02 Mary Doe
...[/cc]
All users with a rating are retrieved as follows,
[cc lang="xquery"]for $user in collection("USERS")/USERS where $user/RATING/@xsi:nil = "true" return $user[/cc]
To me the xsi:nil approach looks much more verbose. DataDirect XQuery represents SQL NULL through an absent element.
Accessing relational data through XQuery
We haven’t yet explained in detail how to reference, to subsequently query, a SQL table in your XQuery. As you have figured out already from the previous examples, we reference a SQL table through the collection() function, passing as argument the SQL table name. Note that this is a convention specific to DataDirect XQuery. As XQuery doesn’t offer a standard way to access your relational database, other products might have chosen a different approach.
Next are equivalent SQL and XQuery queries,
[cc lang="sql"]select * from USERS
for $u in collection("USERS")/USERS return $u[/cc]
If you want to query a table in a specific schema,
[cc lang="xquery"]select * from MYAUCTION.USERS
for $u in collection("MYAUCTION.USERS")/USERS return $u[/cc]
And of course similar if you want to query a table in a particular catalog.
The XQuery Data Model
In this post we learned how to represent relational data in XML, we’ll use this XML representation throughout our subsequent posts in this series.
But of course that there is much more to say about the XQuery Data Model. However this is out of scope for today, we’ll point out some of the major characteristics in a later post in this series. Also note there are various good books covering the subject, or check out the XQuery 1.0 and XPath 2.0 Data Model.
Next we’ll have a closer look into joins.