Skip to content

create-alter-and-drop-selective-xml-indexes.md

Latest commit

 

History

History
79 lines (57 loc) · 3.1 KB

create-alter-and-drop-selective-xml-indexes.md

File metadata and controls

79 lines (57 loc) · 3.1 KB
title Create, Alter, and Drop Selective XML Indexes | Microsoft Docs
ms.custom
ms.date 03/03/2017
ms.prod sql
ms.prod_service database-engine
ms.reviewer
ms.technology xml
ms.topic conceptual
ms.assetid c398f396-f630-4a2d-a264-f243c5346de1
author MightyPen
ms.author genemi

Create, Alter, and Drop Selective XML Indexes

[!INCLUDEappliesto-ss-asdb-xxxx-xxx-md] 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).

Creating a Selective XML Index

How to: Create a Selective XML Index

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

Example

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)  
)

Altering a Selective XML Index

How to: Alter a Selective XML Index

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

Example

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 topic 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  
)

Dropping a Selective XML Index

How to: Drop a Selective XML Index

Drop a Selective XML Index by Using Transact-SQL
Drop a 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 sxi_index ON tbl