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