Examples of XML index usage by predicates that test for node existence

If an XMLEXISTS predicate contains the fn:exists or fn:not function, it matches an XML index that contains the fn:exists or fn:not function.

Example: The following query retrieves all customerinfo documents from the INFO column of the CUSTOMER table for which the address node has a zip attribute.

SELECT INFO FROM CUSTOMER
 WHERE XMLEXISTS('$x/customerinfo/address[fn:exists(@zip)]' 
passing CUSTOMER.INFO as "x")

The following query retrieves all customerinfo documents from the INFO column of the CUSTOMER table for which the address node does not have a zip attribute.

SELECT INFO FROM CUSTOMER
 WHERE XMLEXISTS('$x/customerinfo/address[fn:not(@zip)]' 
passing CUSTOMER.INFO as "x")

Both of these queries can use the following XML index:

CREATE INDEX CUST_ZIP_EXISTENCE on CUSTOMER(INFO)
 GENERATE KEY USING XMLPATTERN '/customerinfo/address/fn:exists(@zip)'
 AS VARCHAR(1)

For queries that test for existence, VARCHAR(1) is the compatible data type for the XML index, because the index key values can be only 'T' or 'F'.