Lesson 6: Querying XML data

This lesson shows you how to query XML data by using SQL, XQuery (with XQuery expressions), or a combination of both.

If you use only SQL, you can query only at the column level. That is, you can return an entire XML document stored in the column, but you cannot query within the document or return fragments of the document. To query values within an XML document or return fragments of a document, you must use XQuery.

The queries in this lesson use XQuery in an SQL context and SQL in an XQuery context.

Important: XQuery is case sensitive, but SQL is not. Therefore, when using XQuery, carefully specify names such as table and SQL schema names, which are both uppercase by default. Even in an SQL context, XQuery expressions remain case sensitive.

Querying in an SQL context

Retrieving entire XML documents

To retrieve all of the XML documents stored in the column named INFO and values from the CID primary key column, issue the following SELECT statement:

SELECT cid, info FROM customer~

This query returns the two stored XML documents.

Retrieving and filtering XML values

To query within the XML documents in the INFO column, issue the following SELECT statement, which uses the XMLQUERY function to invoke an XQuery expression:

SELECT XMLQUERY (
  'declare default element namespace "http://posample.org";
   for $d in $doc/customerinfo
   return <out>{$d/name}</out>'
   passing INFO as "doc") 
FROM Customer as c
WHERE XMLEXISTS ('declare default element namespace "http://posample.org";
   $i/customerinfo/addr[city="Toronto"]' passing c.INFO as "i")~

In the XMLQUERY function, a default namespace is first specified. This namespace matches the namespace of the documents previously inserted. The for clause specifies iteration through the <customerinfo> elements in each document from the Info column. The INFO column is specified by using the passing clause, which binds the INFO column to the variable named doc that is referenced in the for clause. The return clause then constructs an <out> element, which contains the <name> element from each iteration of the for clause.

The WHERE clause uses the XMLEXISTS predicate to consider only a subset of the documents in the Info column. This filtering yields only those documents that have a <city> element (along the path specified) with a value of Toronto.

The SELECT statement returns the following constructed element:

<out xmlns="http://posample.org"><name>Kathy Smith</name></out>

Using db2-fn:sqlquery with parameters

To pass a value to the SQL fullselect in the db2-fn:sqlquery function, run the following query:

VALUES XMLQUERY (
  'declare default element namespace "http://posample.org";
  for $d in db2-fn:sqlquery(
    ''SELECT INFO FROM CUSTOMER WHERE Cid = parameter(1)'', 
    $testval)/customerinfo
  return <out>{$d/name}</out>'
  passing 1000 as "testval" )~

The XMLQUERY function passes the value 1000 to the XQuery expression by using the identifier testval. The XQuery expression then passes the value to the db2-fn:sqlquery function by using the PARAMETER scalar function.

The XQuery expression returns the following constructed element:

<out xmlns="http://posample.org">
   <name>Kathy Smith</name>
</out>

Querying in an XQuery context

Db2® XQuery offers two built-in functions specifically for use with Db2 databases: db2-fn:sqlquery and db2-fn:xmlcolumn. db2-fn:sqlquery retrieves a sequence that is the result table of an SQL fullselect. db2-fn:xmlcolumn retrieves a sequence from an XML column.

If your query invokes an XQuery expression directly, you must prefix it with the case-insensitive keyword XQUERY.

Note: There are several options that you can set to customize your command-line processor environment, particularly for displaying the results of an XQuery expression. For example, set the -i option to make the results from XQuery expressions easier to read, as follows:

UPDATE COMMAND OPTIONS USING i ON~

Retrieving entire XML documents

To retrieve all of the XML documents previously inserted into the INFO column, you can use XQuery with either db2-fn:xmlcolumn or db2-fn:sqlquery.

Using db2-fn:xmlcolumn

To retrieve all XML documents in the INFO column, run the following query:

XQUERY db2-fn:xmlcolumn ('CUSTOMER.INFO')~

Names in SQL statements are automatically converted to uppercase by default. Therefore, when you created the CUSTOMER table by using the CREATE TABLE SQL statement, the names of the table and columns were made uppercase. Because XQuery is case sensitive, you must be careful to use the correct case when specifying the table and column names when using db2-fn:xmlcolumn.

This query is equivalent to the SQL query SELECT Info FROM Customer.

Using db2-fn:sqlquery

To retrieve all XML documents in the INFO column, run the following query:

XQUERY db2-fn:sqlquery ('SELECT Info FROM Customer')~

You do not have to specify the INFO and CUSTOMER names in uppercase because the SELECT statement is processed in an SQL context and is therefore not case sensitive.

Retrieving partial XML documents

Instead of retrieving an entire XML document, you can retrieve fragments of the document and filter on values present in the document by using XQuery with either db2-fn:xmlcolumn or db2-fn:sqlquery.

Using db2-fn:xmlcolumn

To return elements containing <name> nodes for all documents in the Info column that have a <city> element (along the path specified) with a value of Toronto, run the following query:

XQUERY declare default element namespace "http://posample.org"; 
  for $d in db2-fn:xmlcolumn('CUSTOMER.INFO')/customerinfo 
  where $d/addr/city="Toronto" 
  return <out>{$d/name}</out>~

The db2-fn:xmlcolumn function retrieves a sequence from the INFO column of the CUSTOMER table. The for clause binds the variable $d to each <customerinfo> element in the CUSTOMER.INFO column. The where clause restricts the items to only those that have a <city> element (along the path specified) with a value of Toronto. The return clause constructs the returned XML value. This value is an element <out> that contains the <name> element for all documents that satisfy the condition specified in the where clause, as follows:

<out xmlns="http://posample.org">
<name>
              Kathy Smith
</name>
</out>

Using db2-fn:sqlquery

To issue a fullselect within an XQuery expression, run the following query:

XQUERY declare default element namespace "http://posample.org";
  for $d in db2-fn:sqlquery(
    'SELECT INFO
     FROM CUSTOMER
     WHERE Cid < 2000')/customerinfo
  where $d/addr/city="Toronto"
  return <out>{$d/name}</out>~

In this example, the set of XML documents being queried is first restricted, in the fullselect, by particular values in the non-XML CID column. This example demonstrates an advantage of db2-fn:sqlquery: it allows SQL predicates to be applied within an XQuery expression. The documents that result from the SQL query are then further restricted in the where clause of the XQuery expression to those documents that have a <city> element (along the path specified) with a value of Toronto.

The query yields the same results as in the previous example, which used db2-fn:xmlcolumn:

<out xmlns="http://posample.org">
<name>
              Kathy Smith
</name>
</out>

Using db2-fn:sqlquery with parameters

To pass a value to the SQL fullselect in the db2-fn:sqlquery function, run the following query:

XQUERY declare default element namespace "http://posample.org";
  let $testval := 1000
  for $d in db2-fn:sqlquery(
    'SELECT INFO FROM CUSTOMER WHERE Cid = parameter(1)', 
    $testval)/customerinfo
  return <out>{$d/name}</out>~

In the XQuery expression, the let clause sets the value of $testval to 1000. In the for clause, the expression then passes the value to the db2-fn:sqlquery function using the PARAMETER scalar function.

The XQuery expression returns the following constructed element:

<out xmlns="http://posample.org">
   <name>Kathy Smith</name>
</out>

Return to the tutorial