Create, Alter, and Drop Secondary Selective XML Indexes

 

Applies To: SQL Server 2016

Describes how to create a new secondary selective XML index, or alter or drop an existing secondary selective XML index.

How to: Create a Secondary Selective XML Index

Create a Secondary Selective XML Index by Using Transact-SQL
Create a secondary selective XML index by calling the CREATE XML INDEX statement. For more information, see CREATE XML INDEX (Selective XML Indexes).

Example

The following example creates a secondary selective XML index on the path 'pathabc'. The path to index is identified by the name that was given to it when it was created with the CREATE SELECTIVE XML INDEX statement. For more information, see CREATE SELECTIVE XML INDEX (Transact-SQL).

CREATE XML INDEX filt_sxi_index_c  
ON Tbl(xmlcol)  
USING XML INDEX sxi_index  
FOR  
(  
    pathabc  
)  

In This Topic

The ALTER statement is not supported for secondary selective XML indexes. To change a secondary selective XML index, drop the existing index and recreate it.

How to: Alter a Secondary Selective XML Index

Alter a Secondary Selective XML Index by Using Transact-SQL

  1. Drop the existing secondary selective XML index by calling the DROP INDEX statement. For more information, see DROP INDEX (Selective XML Indexes).

  2. Recreate the index with the desired options by calling the CREATE XML INDEX statement. For more information, see CREATE XML INDEX (Selective XML Indexes).

Example

The following example changes a secondary selective XML index by dropping it and recreating it.

DROP INDEX filt_sxi_index_c  
  
CREATE XML INDEX filt_sxi_index_c  
ON Tbl(xmlcol)  
USING XML INDEX sxi_index  
FOR  
(  
    pathabc  
)  

In This Topic

How to: Drop a Secondary Selective XML Index

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

Example

The following example shows a DROP INDEX statement.

DROP INDEX ssxi_index  
ON tbl  

In This Topic

Selective XML Indexes (SXI)

Community Additions

ADD
Show: