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

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

Those conditions are:

• In the XMLEXISTS predicate, the second argument of the fn:starts-with function must be a string literal.
• The XML index must have the VARCHAR type.
• The pattern expression in the index must match the XPath expression in the predicate, except for the fn:starts-with function.

Example: The following query includes a predicate that checks whether the productName value starts with the string "Lapis".

SELECT PORDER FROM PURCHASEORDER
 WHERE XMLEXISTS(
 '/purchaseOrder/items/item[fn:starts-with(productName,"Lapis")]' 
 PASSING PURCHASEORDER.PORDER)

The following index matches the predicate.

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