SQL | XML index
An XML index is an index type that is specifically built to handle indexing XML type columns.
Why use an XML index?
The main reason for creating indexes on XML columns is performance.
The BLOB datatype is used to store XML columns, which can occasionally be rather big, making runtime XML column shredding time-consuming. This time can be significantly decreased by including both primary and secondary indexes in your XML column, however they do come with some overhead when creating/maintaining them.
There are two different types of XML indexes, primary and secondary.
A primary XML index
An XML Index, stores and indexes all the parts of data in your chosen XML column. A primary XML index requires that there be a clustered primary key index on your table so the XML index can correlate rows from the XML index with rows in the table that contain the XML column.
There can only be one primary XML index on a table.
Secondary XML index
Since primary XML indexes index the entire column, which makes them quite large in most cases, you can also add secondary XML indexes which further enhances performance by creating another index on top of the primary XML index.
secondary XML indexes Can be of 3 Types: -
A PATH index is one that is created on the node path and can be used to speed up queries that use path expressions, most commonly the exist() method on XML columns in the WHERE clause.
VALUE indexes are made up of keys of the node value and node path and are most useful when you are searching for a specific value but don't know the path.
PROPERTY index is built on the base tables primary key along with the node path and node value. This type is useful when you know the primary key value of the base table and you are using the value() method on the XML column.
Creating secondary XML indexes also follows this pattern but there are two clauses that also need to be added.
Confirm Index Usage
The following query using XQuery will get a count of student whose gender is "Male".