XMLEXISTS predicate for querying XML data

The XMLEXISTS predicate can be used to restrict the set of rows that a query returns, based on the values in XML columns.

The XMLEXISTS predicate specifies an XQuery expression. If the XQuery expression returns an empty sequence, the value of the XMLEXISTS predicate is false. Otherwise, XMLEXISTS returns true. Rows that correspond to an XMLEXISTS value of true are returned.

XQuery is case-sensitive, so you need to ensure that the variables that you specify in an XMLEXISTS function and in its XQuery expression have the same case.

Important: Because a logical expression or a comparison expression always results in a boolean value of true or false, an XMLEXISTS predicate with a top-level logical expression or comparison expression is always true. For example, the following XMLEXISTS predicate is always true:

XMLEXISTS('//addr/city="Toronto"')

This is usually not the result that you need when you use an XMLEXISTS predicate to filter results.

Example of an XMLEXISTS predicate: Return only those rows from the sample CUSTOMER table for which the <city> value in the INFO column is Toronto and the Cid attribute value in the INFO column is 1004. The XMLEXISTS predicates in the following SELECT statement return true for the appropriate INFO values.

SELECT Cid, Info
FROM CUSTOMER
WHERE XMLEXISTS ('declare default element namespace "http:⁄⁄posample.org";
                 ⁄⁄addr[city="Toronto"]' passing INFO)
AND XMLEXISTS ('declare default element namespace "http:⁄⁄posample.org";
                 ⁄customerinfo[@Cid="1004"]' passing INFO)

The result table contains the following rows:

Table 1. Example of a result table from a SELECT with an XMLEXISTS predicate
CID	INFO
1004	<?xml version="1.0" encoding="IBM037"?><customerinfo xmlns="http://posample.org" Cid=''1004''><name>Matt Foreman</name> <addr country=''Canada''><street>1596 Baseline</street> <city>Toronto</city><prov-state>Ontario</prov-state> <pcode-zip>M3Z-5H9</pcode-zip></addr> <phone type=''work''>905-555-4789</phone> <phone type=''home''>416-555-3376</phone> <assistant><name>Gopher Runner</name> <phone type=''home''>416-555-3426</phone> </assistant> </customerinfo>
1005	<?xml version="1.0" encoding="IBM037"?><customerinfo xmlns="http://posample.org" Cid=''1004''><name>Matt Foreman</name> <addr country=''Canada''><street>1596 Baseline</street> <city>Toronto</city><prov-state>Ontario</prov-state> <pcode-zip>M3Z-5H9</pcode-zip></addr> <phone type=''work''>905-555-4789</phone> <phone type=''home''>416-555-3376</phone> <assistant><name>Gopher Runner</name> <phone type=''home''>416-555-3426</phone> </assistant> </customerinfo>