Data types associated with pattern expressions

Every XML pattern expression that you specify in a CREATE INDEX statement must be associated with a data type. The data type must be VARCHAR, DECFLOAT, DATE, or TIMESTAMP.

If a pattern expression contains fn:exists, the data type that is associated with the pattern expression must be VARCHAR(1).

You can interpret the result of pattern expression as multiple data types. For example, the value 123 has a character representation, but it can also be interpreted as the number 123. You can create different indexes on the same pattern expression with different data types, so that the data can be indexed, regardless of its data type.

Example: Create indexes for the character or numeric representation of the Cid attribute in XML documents in the Info column of the sample Customer table:

CREATE INDEX CUST_XMLIDX_CHAR on Customer(Info)
 GENERATE KEY USING XMLPATTERN '/customerinfo/@Cid' AS SQL VARCHAR(4) 

CREATE INDEX CUST_XMLIDX_NUM on Customer(Info)
 GENERATE KEY USING XMLPATTERN '/customerinfo/@Cid' AS SQL DECFLOAT

Example: Create an index for the date representation of the shipDate element in XML documents in the PORDER column of the PURCHASEORDER table.

CREATE INDEX PO_XMLIDX1 ON PURCHASEORDER (PORDER)
 GENERATE KEY USING XMLPATTERN '//items/shipDate'
 AS SQL DATE