DB2 10.5 for Linux, UNIX, and Windows

Data retrieval with XQuery

XQuery expressions can be executed either using XQuery as the primary language or using SQL with the XMLQUERY SQL function as the primary language. When an XQuery expression is executed using either method, an XML sequence is returned. The XQuery specification defines the result of an XQuery expression as a sequence that contains 0, 1, or more items.

How the resulting sequence appears in a result set differs depending on whether SQL or XQuery is used as the primary language:

XQuery as the primary language: When an XQuery expression is executed using XQuery as the primary language, the result is returned to a client application as a result table with one column, which is of type XML. Each row in this result table is an item of the sequence that resulted from the evaluation of the XQuery expression. When an application fetches from this result table using a cursor, each fetch retrieves a serialized item of the resulting sequence.
SQL as the primary language, using XMLQUERY: XMLQUERY is a scalar function that returns an XML value. The value that is returned is a sequence of 0, 1, or more items. All items of the resulting sequence are returned to an application as a single serialized value.

To fetch results from queries that use XQuery or XMLQUERY, fetch results from within your application, as you normally would any other result set. Bind your application variable to the result set and fetch until the end of the result set. If the XQuery expression (issued directly or through XMLQUERY) returned an empty sequence, then the row in the result set is also empty.

Managing query result sets

If your application requires that the XML values returned when querying with XQuery are well-formed XML documents (for example, if you plan to insert these values into a column of type XML), then you can ensure that the values are well-formed XML documents by including an element or document constructor in your XQuery expression.

Example: Difference in result sets from XQuery and XMLQUERY

This example illustrates the difference between the result sets from the two querying methods.

If the following two XML documents are stored in an XML column, to retrieve all <phone> elements, you can use either XQuery or XMLQUERY. The result sets that are returned by these two methods differ, however, and should be handled accordingly by the application when fetching from the result set.

<customerinfo Cid="1000">
  <name>Kathy Smith</name>
  <addr country="Canada">
    <street>5 Rosewood</street>
    <city>Toronto</city>
    <prov-state>Ontario</prov-state>
    <pcode-zip>M6W 1E6</pcode-zip>
  </addr>
  <phone type="work">416-555-1358</phone>
</customerinfo>

<customerinfo Cid="1003">
  <name>Robert Shoemaker</name>
    <addr country="Canada">
    <street>1596 Baseline</street>
    <city>Aurora</city>
    <prov-state>Ontario</prov-state>
    <pcode-zip>N8X 7F8</pcode-zip>
  </addr>
  <phone type="work">905-555-7258</phone>
  <phone type="home">416-555-2937</phone>
  <phone type="cell">905-555-8743</phone>
  <phone type="cottage">613-555-3278</phone>
</customerinfo>

Executing an XQuery expression using XQuery as the primary language such as the following example:

XQUERY
  db2-fn:xmlcolumn ('CUSTOMER.INFO')/customerinfo/phone

The result set returns five rows, as follows:

<phone type="work">416-555-1358</phone>
<phone type="work">905-555-2937</phone>
<phone type="home">416-555-2937</phone>
<phone type="cell">905-555-8743</phone>
<phone type="cottage">613-555-3278</phone>

Executing an XQuery expression through XMLQUERY such as the following example:

SELECT XMLQUERY ('$doc/customerinfo/phone' PASSING INFO AS "doc")
FROM CUSTOMER

The result set returns two rows, as follows, where all <phone> elements of the second row in the table are concatenated in a single scalar value (an XML sequence):

<phone type="work">416-555-1358</phone>
<phone type="work">905-555-2937</phone><phone type="home">416-555-2937</phone><phone type="cell">905-555-8743</phone><phone type="cottage">613-555-3278</phone>

Notice that the second row of this result set contains a value that is not a well-formed XML document.

These differences in the result sets exist because XMLQUERY is a scalar function. It executes on each row of the table and the resulting sequence from a row of the table, forms a row of the result set. XQuery, however, returns each item of a sequence as a separate row of the result set.

Example: Managing query result sets

In this example, the previous SQL query can be modified to include an XQuery document node constructor that ensures the resulting rows all contain well-formed documents:

SELECT XMLQUERY ('document{<phonelist>{$doc/customerinfo/phone}</phonelist>}'
  PASSING INFO AS "doc")
FROM CUSTOMER

The result set from this query returns two rows, assuming the same documents presented previously exist in database, is as follows (The output has been formatted for clarity.

<phonelist><phone type="work">416-555-1358</phone></phonelist>
<phonelist><phone type="work">905-555-7258</phone><phone type="home">416-555-2937</phone><phone type="cell">905-555-8743</phone><phone type="cottage">613-555-3278</phone></phonelist>