Example of index usage for an XMLEXISTS predicate with the fn:substring function

An XMLEXISTS predicate that contains the fn:substring function and an XML index need to meet the several conditions for an index match.

Those conditions are:

• In the XMLEXISTS predicate:
  • The second argument of the fn:substring functions must be 1.
  • The operand to which the expression that contains the fn:substring function is compared is a string literal.
  • The third argument of the fn:substring function must be an integer constant that is equal to the length of the string literal.
• The pattern expression in the index must match the XPath expression in the predicate, except for the fn:substring function.

Example: The following query includes a predicate that checks whether the productName value is the string "Lapis", with the characters in uppercase or lowercase.

SELECT PORDER FROM PURCHASEORDER
 WHERE XMLEXISTS(
 '/purchaseOrder/items/item[fn:substring(productName, 1,5)= "LAPIS"]' 
 PASSING PURCHASEORDER.PORDER)

The following index matches the predicate.

CREATE INDEX POSUBSTR ON PURCHASEORDER(PORDER)
 GENERATE KEYS USING XMLPATTERN 
 '/purchaseOrder/items/item/productName'
 AS SQL VARCHAR(20)