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
Σχόλια
https://aka.ms/ContentUserFeedback.
Σύντομα διαθέσιμα: Καθ' όλη τη διάρκεια του 2024 θα καταργήσουμε σταδιακά τα ζητήματα GitHub ως μηχανισμό ανάδρασης για το περιεχόμενο και θα το αντικαταστήσουμε με ένα νέο σύστημα ανάδρασης. Για περισσότερες πληροφορίες, ανατρέξτε στο θέμα:Υποβολή και προβολή σχολίων για