When a query is based on an XML column, the query processor needs to parse the XML data each time the query is executed. In SQL Server, and XML data value can be of a maximum of two gigabytes (GB).
Therefore, the XML values can be very large and the server might take time to generate the result set. To speed up the execution of the query based on the XML data type, SQL Server allows you to create an index that is based on columns storing XML data values. Such indexes are called XML indexes.
You can create XML indexes on XML columns by using the CREATE PRIMARY XML INDEX and CREATE XML INDEX T-SQL commands. For example, the ProductModel table contains the CatalogDescription column that stores XML values. You can create a primary XML index on this column by using the following statement:
CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDesctiption ON Production.ProductModel (CatalogDescription)
The preceding statement will create an index for all the nodes in the XML data stored in the CatalogDescription column.
The three types of secondary XML indexes are:
Therefore, the XML values can be very large and the server might take time to generate the result set. To speed up the execution of the query based on the XML data type, SQL Server allows you to create an index that is based on columns storing XML data values. Such indexes are called XML indexes.
Primary XML Index
This is a clustered B-Tree representation of the nodes in the XML data. When an index is created on a column with the XML data type, an entry will be created for all the nodes in the XML data. Therefore, the index creates several rows of data for each XML value in the column.You can create XML indexes on XML columns by using the CREATE PRIMARY XML INDEX and CREATE XML INDEX T-SQL commands. For example, the ProductModel table contains the CatalogDescription column that stores XML values. You can create a primary XML index on this column by using the following statement:
CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDesctiption ON Production.ProductModel (CatalogDescription)
The preceding statement will create an index for all the nodes in the XML data stored in the CatalogDescription column.
Secondary XML Index
This is a non-clustered index of the primary XML index. A primary XML index must exist before any secondary index can be created. After you have created the primary XML index, an additional three kinds of secondary XML indexes can be defined on the table. The secondary XML indexes assist in the XQuery processing.The three types of secondary XML indexes are: