DB2 10.5 for Linux, UNIX, and Windows

Non-empty sequences returned by XMLQUERY

The XMLQUERY function returns a non-empty sequence, if the XQuery expression specified within it results in a non-empty sequence.

For example, consider the following two XML documents that are stored in the XML column INFO of the CUSTOMER table:

<customerinfo Cid="1002">
  <name>Jim Noodle</name>
  <addr country="Canada">
    <street>25 EastCreek</street>
    <city>Markham</city>
    <prov-state>Ontario</prov-state>
    <pcode-zip>N9C 3T6</pcode-zip>
  </addr>
  <phone type="work">905-555-7258</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>

If you issue the following query

SELECT XMLQUERY ('$d/customerinfo/phone' passing INFO as "d")
FROM CUSTOMER

the results set contains two rows as follows (the ouput has been formatted for clarity):

1
-----------

<phone type="work">905-555-7258</phone>
<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>

  2 record(s) selected.

Notice that the first row contains a sequence of one <phone> element, while the second row has a sequence of four <phone> elements. This result occurs because the second XML document contains four <phone> elements, and XMLQUERY returns a sequence of all elements that satisfy the XQuery expression. (Note that the result in the second row is not a well-formed document. Ensure that any application receiving this result can properly handle this behavior.)

The previous example shows how XMLQUERY is commonly used: applied to one XML document at a time, where each row in the resulting table represents the result from one document. XMLQUERY, however, can also be applied to multiple documents at once, as is the case when multiple documents are contained in a single sequence. In this case, the results from applying XMLQUERY to all documents in the sequence are returned in a single row.

For example, assume that the same documents presented previously are stored in the INFO column of the CUSTOMER table. The db2-fn:xmlcolumn function in the following query returns one sequence that contains the two XML documents in the INFO column.

VALUES
 (XMLQUERY
   ('db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo/phone'))

XMLQUERY is then applied to this single sequence of XML documents, and the result set contains only one row, as follows:

1
-----------

<phone type="work">905-555-7258</phone><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>

  1 record(s) selected.

All <phone> elements from the XML documents in the INFO column are returned in a single row, because XMLQUERY operates on a single value: the sequence of XML documents returned from db2-fn:xmlcolumn.