Examples of XML index usage by equal predicates

Examples demonstrate the use of XML indexes by equal predicates.

Example: The following query includes an equal predicate on a string type. It retrieves all documents from the INFO column of the CUSTOMER table for customers whose zip code is 95141.

SELECT INFO FROM CUSTOMER
 WHERE XMLEXISTS('$x/customerinfo/address[@zip=”95141”]'
 PASSING CUSTOMER.INFO AS "x")

To be compatible with this query, the XML index needs to include the zip attribute node among the indexed nodes, and to store values in the index as a VARCHAR type.

The query can use this XML index:

CREATE INDEX CUST_ZIP_STR on CUSTOMER(INFO)
	GENERATE KEY USING XMLPATTERN '/customerinfo/address/@zip' 
  AS VARCHAR(10)

Example: Suppose that you change the query in the previous example to look like this:

SELECT INFO FROM CUSTOMER
 WHERE XMLEXISTS('$x//address[@zip=”95141”]' 
 PASSING CUSTOMER.INFO AS "x")

Index CUST_ZIP_STR in the previous example cannot be used for this query because the XPath expression in the XMLEXISTS predicate now specifies a superset of the nodes that the index specifies. In the query, the zip attribute node is under an address element that is the descendant of any node. Index CUST_ZIP_STR specifies only the zip attribute under the address element that is a child of the customerinfo element. Define an index like this for use with the query in this example:

CREATE INDEX CUST_ZIP_STR2 ON CUSTOMER(INFO)
	GENERATE KEY USING XMLPATTERN '//address/@zip' 
  AS VARCHAR(10)

Example: The following query includes an equal predicate on a numeric type. It retrieves documents from the DESCRIPTION column of the PRODUCT table for items with a price equal to 9.99.

SELECT INFO FROM CUSTOMER
 WHERE XMLEXISTS('$x//address[@zip="95141"]' 
 PASSING CUSTOMER.INFO AS "x")

To be compatible, the XML index needs to include price nodes among the indexed nodes, and to store values as the DECFLOAT type.

The query can use this XML index:

CREATE INDEX PRODINDEX ON PRODUCT(DESCRIPTION)
 GENERATE KEY USING XMLPATTERN '//price' AS SQL DECFLOAT

Example: The following query includes an equal predicate on a text node. It retrieves all documents from the Info column of the sample Customer table for which the assistant name is Gopher Runner.

SELECT INFO FROM CUSTOMER
 WHERE XMLEXISTS('$x/customerinfo/assistant[name="Gopher Runner"]' 
 PASSING BY REF INFO AS "x")

To be compatible with this query, the XML index needs to include the text node within the name element that is under the assistant element, and needs to store values in the index as a VARCHAR type.

The query can use this XML index:

CREATE INDEX CUSTINDEX on CUSTOMER(INFO)
 GENERATE KEY USING XMLPATTERN '/customerinfo/assistant/name/text()'
 AS SQL VARCHAR(20)