Lesson 2: Creating indexes over XML data
This lesson shows you how to create an index over XML data. Indexes over XML data can improve the performance of queries on XML columns. Index XML elements or attributes that you frequently use in predicates and cross-document joins.
A relational index and an index over XML data both index a column. However, a relational index indexes an entire column, and an index over XML data indexes part of a column. You indicate which parts of an XML column to index by specifying an XML pattern, which is a limited XPath expression. You also must specify the data type that the indexed values will be stored as. Generally, the type that you choose should be the same type that you use in queries.
Like relational indexes, it is recommended that you index XML elements or attributes that are frequently used in predicates and cross-document joins.
You can index only a single XML column; composite indexes are not supported. You can have multiple indexes on an XML column, however.
Not all clauses of the CREATE INDEX statement apply to indexes over XML data. Refer to the CREATE INDEX statement for details.
CREATE INDEX cust_cid_xmlidx ON Customer(Info)
GENERATE KEY USING XMLPATTERN
'declare default element namespace "http://posample.org"; /customerinfo/@Cid'
AS SQL DOUBLE~The statement indexes the values of the Cid attribute of <customerinfo> elements from the INFO column of the CUSTOMER table. As the default, when the XML data is indexed, if the XML data fails to cast to the specified data type, SQL DOUBLE, no index entry is created, and no error is returned.
The XML pattern that you specify is case sensitive. If, for example, the XML documents contained the attribute cid instead of Cid, those documents would not match this index.