What Is a XML Indexes in Microsoft SQL Server?
A primary XML index stores a shredded XML representation. Secondary PATH, VALUE, or PROPERTY indexes target distinct XQuery access patterns.
How the XML Indexes Algorithm Works
A primary XML index stores a shredded XML representation. Secondary PATH, VALUE, or PROPERTY indexes target distinct XQuery access patterns.
When to Use XML Indexes
XML column searches when extracting the data into relational columns is not practical.
How to Create a Microsoft SQL Server XML Indexes
Review data quality, existing structures, feature support, build time, and deployment requirements before creating an index on a production table.
CREATE PRIMARY XML INDEX PX_Documents_Xml ON dbo.Documents (XmlData);
CREATE XML INDEX SX_Documents_Path ON dbo.Documents (XmlData) USING XML INDEX PX_Documents_Xml FOR PATH;XML Indexes Query Example
This example demonstrates the matching query shape. Check the execution plan to confirm that the engine can use the index.
SELECT DocumentId FROM dbo.Documents WHERE XmlData.exist('/Order/Line[@sku="A104"]') = 1;Queries Supported by XML Indexes
XML column searches when extracting the data into relational columns is not practical. A similar-looking predicate may not use the same access path; operators, expressions, key order, and data type all matter.
Read Performance, Storage, and Write Costs
Indexes can be large and costly to maintain; secondary XML indexes require a primary XML index. Indexes reduce some search work but require storage and add maintenance to writes.
XML Indexes Limitations and Common Mistakes
Index types have prerequisites and different path/value/property behavior.
Do not assume that an index being present means the optimizer chose it or that the query became selective.
How to Measure and Tune XML Indexes
Test actual XQuery and plans; consider relational columns for frequently queried attributes.
Compare representative plans and timings before and after, including concurrent writes and realistic data volumes.
XML Indexes Questions
Will the database always use this index?
No. The optimizer weighs selectivity, estimated costs, statistics, cache state, and the amount of data returned.
What should I verify before production?
Confirm version support, query compatibility, write overhead, build/rollback procedures, and index behavior during backup and recovery.
Official Microsoft SQL Server Index Documentation
Check Microsoft Learn: SQL Server Indexes for release-specific requirements and supported operators.