Create, alter, and drop selective XML indexes
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
Athugasemdir
https://aka.ms/ContentUserFeedback.
Væntanlegt: Á árinu 2024 munum við hætta með GitHub-mál sem ábendingaleið fyrir efni og skipta því út fyrir nýtt ábendingakerfi. Frekari upplýsingar er að finna hér:Senda inn og skoða endurgjöf fyrir