blog.mycado.fr

Queries on XML data with XQuery

We know how to store native XML data in our DB2 tables, and now we will see how we can access to these data. We can choose between standard SQL queries and XQuery.. or both ! The first solution, with a SQL query, only query at the column level of your table; this query will return the full XML data. The second solution, with XQuery, allow us to make a “query” inside our XML data.

For the SQL query, nothing more than a SELECT:

SELECT id, info from client

Easy, but not really powerful for XML data, let’s try with XQuery, who give us two functions for DB2. db2-fn:sqlquery and db2-fn:xmlcolumn. The first function retrieves a sequence that is the result of an SQL fullselect and the second retrieves a sequence from a column. One important thing you should keep in mind, SQL is not a case-sensitive language alors que XQuery is a case-sensitive language.

An example which return all the XML data from the “info” column:

XQUERY db2-fnxmlcolumn ('CLIENT.INFO')

Which is same as this SQL query:

SELECT info FROM client

Let’s see something more nice. This query will return all the elements in <name> , inside the “info” column, and with the <city> element which containt “Paris”:

XQUERY declare defaut element namespace "http://posample.org";
for $d in db2-fn:xmlcolumn('CLIENT.INFO')/clientinfo
  where $d/addr/city="Paris"
return <out>{$d/name}</out>

db2-fn:xmlcolumn retrieves the data from the “info” column in the “client” table. We add a $d variable, for each element of <clientinfo>, and we use a where to filter the <city> element which should be “Paris. To finish, we use <out> to output the data:

<out xmlns="http://posample.org">
  <name>Sophie Bool</name>
</out>

XQUERY declare default element namespace "http://posample.org";
for $d in db2-fn:sqlquery('SELECT info FROM client')/clientinfo
  where $d/addr/city="Paris
return <out>{$d/name}</out>