Pattern expressions

For an XML document in an XML column, Db2 indexes only the parts that satisfy an XML pattern expression.

To index on an XML pattern, you provide an index specification clause in the CREATE INDEX statement. The index specification clause begins with GENERATE KEY USING XMLPATTERN, followed by an XML pattern and a data type for the XML index.

Only one index specification clause is allowed in a CREATE INDEX statement. However, you can create multiple XML indexes on an XML column.

To identify those parts of the document that you want to index, you use an XML pattern to specify a set of nodes in the XML document. This pattern expression is similar to an XPath expression, but it differs in that only a subset of the XPath language is supported.

The following examples show various pattern expressions that index data in the Info column of the sample Customer table. Statements  1  and  2  are logically equivalent. Statement  2  uses the unabbreviated syntax. Statements  3  and  4  are logically equivalent. Statement  4  uses the unabbreviated syntax.

CREATE INDEX CSTPHIX1 on Customer(Info)                                        1 
 GENERATE KEY USING XMLPATTERN '/customerinfo/phone/@type' AS SQL VARCHAR(12) 

CREATE INDEX CSTPHIX2 on Customer(Info)                                        2 
 GENERATE KEY USING XMLPATTERN '/child::customerinfo/child::phone/attribute::type' 
 AS SQL VARCHAR(12)

CREATE INDEX CSTPHIX3 on Customer(Info)                                        3 
 GENERATE KEY USING XMLPATTERN '//@type' AS SQL DECFLOAT

CREATE INDEX CSTPHIX4 on Customer(Info)                                        4 
 GENERATE KEY USING XMLPATTERN '/descendant-or-self::node()/attribute::type' 
 AS SQL DECFLOAT

You can tailor your pattern expressions to be more specific or more general. For example, suppose that some of the documents in the Info column of the Customer table have an Cid attribute on the name element, as well as on the customerinfo element. That is, the XML documents in the Info column can have either of these two paths: '/customerinfo/@Cid' and '/customerinfo/name/@Cid'. You can write an XML pattern that includes either of these paths, or both paths.

For example, if you want to index on the customer ID for a specific customer, you can create an index with the XML pattern '/customerinfo/name/@Cid'. Queries with predicates of the form '/customerinfo/name[@Cid="1000"]' can use this index.

Alternatively, you can create an XML pattern that indexes the customer ID attribute whether it appears in the customerinfo element or the name element. The pattern expression '//@Cid' does that.

XML pattern expressions can contain the fn:exists or fn:upper-case functions. If a pattern expression contains fn:exists, the data type that is associated with the pattern expression must be VARCHAR(1).