DB2 10.5 for Linux, UNIX, and Windows

XMLEXISTS predicate when querying XML data

The XMLEXISTS predicate determines whether an XQuery expression returns a sequence of one or more items. If the XQuery expression specified in this predicate returns an empty sequence, XMLEXISTS returns false; otherwise, true is returned.

The XMLEXISTS predicate can be used in the WHERE clauses of SELECT statements. This usage means that values from stored XML documents can be used to restrict the set of rows that the SELECT query operates on.

For example, the following SQL query shows how the XMLEXISTS predicate can be used to restrict the rows returned, to only those that contain an XML document with a <city> element that has the value "Toronto". (Note that XQuery expressions are case-sensitive, while SQL is case-insensitive.)

SELECT Cid
FROM CUSTOMER
WHERE XMLEXISTS ('$d//addr[city="Toronto"]' passing INFO as "d")

Note how you can pass values to XQuery variables in the XQuery expression of XMLEXISTS. In this case, the XQuery variable $d is bound to the documents of the INFO column of the CUSTOMER table. A simpler syntax for passing column names without having to specify the names in the passing clause explicitly is also available. See Simple column name passing with XMLEXISTS, XMLQUERY, or XMLTABLE.

Ensure that the XQuery expression in XMLEXISTS is correctly specified in order to return expected results. For example, assume that there are multiple documents stored in the XML INFO column of the CUSTOMER table, but only one document contains a Cid attribute (along the path specified) with a value of 1000:

<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>

The following two queries return different results, because of the slight difference in the XQuery expressions:

SELECT *
FROM CUSTOMER
WHERE XMLEXISTS ('$d/customerinfo[@Cid=1000]' passing INFO as "d")

SELECT *
FROM CUSTOMER
WHERE XMLEXISTS ('$d/customerinfo/@Cid=1000' passing INFO as "d")

The first query returns the row containing the XML document presented previously, as expected. The second query, however, returns all rows of the CUSTOMER table because the XMLEXISTS predicate always returns true for the XQuery expression specified. The XQuery expression in the second query returns a sequence of boolean items, which is a non-empty sequence, causing XMLEXISTS to always return true. This then causes every row in the CUSTOMER table to be selected, which is not the intended result.