Example of XML index usage by join predicates

If an XMLEXISTS predicate contains a join of two tables, the join condition compares two XPath expressions. An XML index that the predicate uses must be on the first table in the join order, and the XPath expression must match both XPath expressions in the join condition.

Example: The following query retrieves XML documents from the CUSTOMER and ORDER tables for which a customer ID in the CUSTOMER table matches the customer ID in the ORDER table. The customer IDs are compared as strings.

SELECT INFO FROM CUSTOMER, ORDER
 WHERE XMLEXISTS('$x/customerinfo[@Cid = $y/order/customer/@id/fn:string(.)]'
 passing CUSTOMER.INFO as "x", ORDER.ORDERINFO as "y")

The first table in the join is the CUSTOMER table, so the query can use the following XML index on the CUSTOMER table:

CREATE INDEX CUST_CID_STR ON CUSTOMER(INFO)
 GENERATE KEYS USING XMLPATTERN
 ‘/customerinfo/@Cid'
 AS SQL VARCHAR(10)

Because the XPath expressions in the join predicate are compared as strings, the index must store entries in the index as the VARCHAR type.

Example: The following query retrieves XML documents from the ORDER and CUSTOMER tables for which a customer ID in the ORDER table matches the customer ID in the CUSTOMER table. The customer IDs are compared as numeric values.

SELECT INFO FROM CUSTOMER, ORDER
 WHERE XMLEXISTS('$y/order/customer[@id = $x/customerinfo/@id/xs:decimal(.)]'
 passing CUSTOMER.INFO as "x", ORDER.ORDERINFO as "y")

The first table in the join is the ORDER table, so the query can use the following XML index on the ORDER table:

CREATE INDEX ORDER_CID_NUM ON ORDER(ORDERINFO)
 GENERATE KEYS USING XMLPATTERN
 ‘/order/customer/@id'
 AS SQL DECFLOAT

Because the XPath expressions in the join predicate are compared as numeric values, the index must store entries in the index as the DECFLOAT type.