XML index attributes

You can create an index on an XML column for efficient evaluation of XQuery expressions to improve performance for queries on XML documents.

In simple relational indexes, index keys are composed of one or more table columns that you specified. However, an XML index uses a particular XML pattern expression to index paths and values in XML documents that are stored in a single XML column.

In an XML index, only the attribute nodes, text nodes, or element nodes that match the XML pattern expression are indexed. An XML index only indexes the nodes that match the specific XML pattern and not the document itself. Two more key fields are added to the index to form the composite index key. The extra key fields, which identify the XML document and the node position within the document, are stored in the catalog. These fields are not involved in uniqueness checking for unique indexes.

Use the CREATE INDEX statement with the XMLPATTERN keyword to create an XML index. You must also specify the XML path to be indexed. An index key is formed by concatenating the values that are extracted from the nodes in the XML document that satisfy the specified XML path with the document and node ID.

You specify a data type for every XML index. XML indexes support the following data types:
  • VARCHAR
  • DECFLOAT
  • TIMESTAMP(12)
  • DATE
You can use the following clauses to control whether Db2 inserts values into a table that are not compatible with the index data type:
  • IGNORE INVALID VALUES
  • REJECT INVALID VALUES

When you index an XML column with XMLPATTERN, only the parts of the document that satisfy the XML pattern expression are indexed. Multiple parts of the document might satisfy the XML pattern that you specified in the XMLPATTERN. Therefore, more than one index key entry might be generated and inserted into the index for the insertion of a single document.

Only one XML index specification is allowed per CREATE INDEX statement. However, you can create an XML index with multiple keys, or create multiple XML indexes on an XML column.

Restriction: Partitioned XML indexes are not supported

Examples

Example 1
Begin general-use programming interface information.Assume that you must search for a specific employee's surname (name/last) on the employee elements. You can use the following CREATE INDEX statement to create an index on the '/department/emp/name/last' XML pattern expression:
CREATE INDEX EMPINDEX ON DEPARTMENT (DEPTDOCS)
		GENERATE KEYS USING XMLPATTERN '/department/emp/name/last'
		AS SQL VARCHAR(20)

After the EMPINDEX index is created successfully, several entries are populated in the catalog tables.

End general-use programming interface information.
Example 2
You can create two XML indexes with the same pattern expression by using different data types for each. You can use the different indexes to choose how you want to interpret the result of the expression as multiple data types. For example, the value '12345' has a character representation but it can also be interpreted as the number 12,345. For example, assume that you want to index the path '/department/emp/@id' as both a character string and a number. You must create two indexes, one for the VARCHAR data type and one for the DECFLOAT data type. The values in the document are cast to the specified data type for each index.