Edit

Create, alter, and drop selective XML indexes

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

This article describes how to create a new selective XML index, or alter or drop an existing selective XML index.

For more information about selective XML indexes, see Selective XML indexes (SXI).

Create a selective XML index

Create a selective XML index using Transact-SQL, by calling the CREATE SELECTIVE XML INDEX statement. For more information, see CREATE SELECTIVE XML INDEX (Transact-SQL).

The following example shows the syntax for creating a selective XML index. It also shows several variations of the syntax for describing the paths to be indexed, with optional optimization hints.

CREATE SELECTIVE XML INDEX sxi_index
ON Tbl(xmlcol)

FOR(
    pathab   = '/a/b' as XQUERY 'node()'
    pathabc  = '/a/b/c' as XQUERY 'xs:double',
    pathdtext = '/a/b/d/text()' as XQUERY 'xs:string' MAXLENGTH(200) SINGLETON
    pathabe = '/a/b/e' as SQL NVARCHAR(100)
)

Alter a selective XML index

Alter an existing selective XML index using Transact-SQL, by calling the ALTER INDEX statement. For more information, see ALTER INDEX (Selective XML Indexes).

The following example shows an ALTER INDEX statement. This statement adds the path '/a/b/m' to the XQuery part of the index and deletes the path '/a/b/e' from the SQL part of the index created in the example in the article CREATE SELECTIVE XML INDEX (Transact-SQL). The path to delete is identified by the name that was given to it when it was created.

ALTER INDEX sxi_index
ON Tbl
FOR
(
    ADD pathm = '/a/b/m' as XQUERY 'node()' ,
    REMOVE pathabe
)

Drop a selective XML index

Drop a selective XML index using Transact-SQL, by calling the DROP INDEX statement. For more information, see DROP INDEX (Selective XML Indexes).

The following example shows a DROP INDEX statement.

DROP INDEX sxi_index ON tbl

See also