Checking whether an XML column contains a certain value

You can determine which rows contain any fragment of XML data that you specify.

Procedure

To check whether an XML column contains a certain value:

Specify the XMLEXISTS predicate in the WHERE clause of your SQL statement.

Include the following parameters for the XMLEXISTS predicate:

An XPath expression that is embedded in a character string literal. Specify an XPath expression that identifies the XML data that you are looking for. If the result of the XPath expression is an empty sequence, XMLEXISTS returns false. If the result is not empty, XMLEXISTS returns true. If the evaluation of the XPath expression returns an error, XMLEXISTS returns an error.
The XML column name. Specify this value after the PASSING keyword.

Example

Suppose that you want to return only purchase orders that have a billing address. Assume that column XMLPO stores the XML purchase order documents and that the billTo nodes within these documents contain any billing addresses. You can use the following SELECT statement with the XMLEXISTS predicate:

SELECT XMLPO FROM T1
  WHERE XMLEXISTS ('declare namespace ipo="http://www.example.com/IPO";
                    /ipo:purchaseOrder[billTo]'
                    PASSING XMLPO);