Selective XML Indexes in SQL Server: Fast Queries on Large XML Columns

Many applications store XML in SQL Server: e-invoices and shipping documents, integration messages, configuration records, audit trails. As tables grow, queries that search inside this XML slow down, because SQL Server has to parse the XML in every row at runtime. The classic fix, a primary XML index, shreds and stores the entire document and often produces an index larger than the table itself.

A selective XML index offers a smarter path between these extremes: it indexes only the XML paths you actually query. This article explains how it works, when it’s the right choice, how to create one and what its limits are.

How It Differs from a Primary XML Index

Microsoft also recommends a selective XML index over a primary XML index in most cases. The exceptions: when a large number of different paths must be indexed, or when queries look for elements whose location in the document isn’t known in advance.

Prerequisites

• A clustered index must exist on the table’s primary key.
• The primary key can be at most 128 bytes, and the clustering key at most 15 columns.
• Each XML column can have only one selective XML index.

Example: Searching an E-Document Table

Consider an XML column holding the document number, the recipient’s tax number, the date and the product codes on the line items. If queries use only these four paths, indexing just those is enough:

CREATE TABLE dbo.EDocument
(
    DocumentId bigint IDENTITY PRIMARY KEY CLUSTERED,
    Content    xml NOT NULL
);

CREATE SELECTIVE XML INDEX SXI_EDocument
ON dbo.EDocument (Content)
FOR
(
    docNo       = '/Document/DocNo'              AS SQL nvarchar(30) SINGLETON,
    recipientId = '/Document/Recipient/TaxId'    AS SQL varchar(11)  SINGLETON,
    docDate     = '/Document/Date'               AS XQUERY 'xs:date' SINGLETON,
    productCode = '/Document/Line/ProductCode'   AS XQUERY 'xs:string'
);

Each path has three parts: a unique name, the XQuery expression describing the path, and optional optimization hints.

• AS SQL type: the value is stored directly as a SQL data type; the most efficient choice for single-valued fields read and compared with value(). Date and time types (date, datetime, datetime2) aren’t allowed here and fail with Msg 6375; index dates with AS XQUERY 'xs:date' instead.
• SINGLETON: states that the path occurs at most once per document, letting the optimizer produce simpler plans. Use it only for truly single-valued fields.
• AS XQUERY type: for paths that can occur several times in a document (for example invoice lines).

Queries that benefit from the index use the indexed values instead of parsing the XML in every row:

-- Documents for a given recipient
SELECT DocumentId, Content.value('(/Document/DocNo)[1]', 'nvarchar(30)') AS DocNo
FROM dbo.EDocument
WHERE Content.exist('/Document/Recipient[TaxId = "1234567890"]') = 1;

-- Documents containing a given product
SELECT DocumentId
FROM dbo.EDocument
WHERE Content.exist('/Document/Line[ProductCode = "PRD-100"]') = 1;

Secondary Selective XML Indexes

If you filter often on a very selective path, you can add a secondary index on that path:

CREATE XML INDEX SXI_EDocument_RecipientId
ON dbo.EDocument (Content)
USING XML INDEX SXI_EDocument FOR (recipientId);

Which Queries Benefit?

Selective XML indexes can fully cover expressions in the exist(), value() and nodes() methods. In the query() and modify() methods, they’re used only for filtering rows, not for producing results or updating XML.

Unsupported features include paths ending in a wildcard such as /a/b/*, axes other than child, attribute and descendant, binary types such as base64Binary, and comment and processing instruction nodes.

Limits

• The index maps XML to a single relational table and uses sparse columns. In practice, the number of nodes that can be indexed ranges from 60 to a few hundred, depending on the data and on the use of //.
• It can’t be created on XML columns in views, table variables, XML variables or computed XML columns.
• It can’t be used on XML nested more than 128 levels deep.

Checklist

• The paths used by the most frequent XML queries are identified.
• A clustered index exists on the table’s primary key.
• Single-valued fields are mapped AS SQL with SINGLETON, repeating fields AS XQUERY.
• Index creation is planned for a maintenance window.
• Moving constantly queried fields into relational columns has been considered.