This topic has not yet been rated - Rate this topic

CREATE SELECTIVE XML INDEX

SQL Server 2012

Creates a new selective XML index on the specified table and XML column.

Selective XML indexes improve the performance of XML indexing and querying by indexing only the subset of nodes that you typically query.

For more information, see the following topics:

You can also create secondary selective XML indexes. For information, see Create, Alter, and Drop Secondary Selective XML Indexes.

Topic link icon Transact-SQL Syntax Conventions

Syntax

Transact-SQL

CREATE SELECTIVE XML INDEX index_name
ON <table_object> (<xml_column_name>)
[WITH XMLNAMESPACES (<xmlnamespace_list>)]
FOR (<promoted_node_path_list>)
[WITH (<index_options>)]

<table_object> ::= { [ database_name. [ schema_name ] . | schema_name. ] table_name }

<promoted_node_path_list> ::= <named_promoted_node_path_item> [, <promoted_node_path_list>]

<named_promoted_node_path_item> ::= <path_name> = <promoted_node_path_item>

<promoted_node_path_item>::=<xquery_node_path_item> | <sql_values_node_path_item>

<xquery_node_path_item> ::= <node_path> [as XQUERY <xsd_type_or_node_hint>] [SINGLETON]

<xsd_type_or_node_hint> ::= [<xsd_type>] [MAXLENGTH(x)] | node()

<sql_values_node_path_item> ::= <node_path> as SQL <sql_type> [SINGLETON]

<node_path> ::= <character_string_literal>

<xsd_type> ::= <character_string_literal>

<sql_type> ::= <identifier>

<path_name> ::= <identifier>

<xmlnamespace_list> ::= <xmlnamespace_item> [, <xmlnamespace_list>]

<xmlnamespace_item> ::= <xmlnamespace_uri> AS <xmlnamespace_prefix>

<xml_namespace_uri> ::= <character_string_literal>

<xml_namespace_prefix> ::= <identifier>

<index_options> ::= ( 
  | PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY =OFF
  | DROP_EXISTING = { ON | OFF }
  | ONLINE =OFF
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
)

Arguments

CREATE clause

index_name: Is the name of the new index to create. Index names must be unique within a table, but do not have to be unique within a database. Index names must follow the rules of identifiers.

For example: CREATE SELECTIVE XML INDEX sxi_index

ON clause

<table_object>

Is the table that contains the XML column to index. Use one of the following formats:

database_name.schema_name.table_name
database_name..table_name
schema_name.table_name
table_name

xml_column_name

Is the name of the XML column that contains the paths to index.

For example: ON Tbl(xmlcol)

WITH XMLNAMESPACES clause

<xmlnamespace_list>: Is the list of namespaces used by the paths to index. For information about the syntax of the WITH XMLNAMESPACES clause, see WITH XMLNAMESPACES (Transact-SQL).

For example: WITH XMLNAMESPACES ('http://www.tempuri.org/' as myns)

FOR clause

<promoted_node_path_list>: Is the list of paths to index with optional optimization hints. For information about the paths and the optimization hints that you can specify in the CREATE or ALTER statement, see Specify Paths and Optimization Hints for Selective XML Indexes.

For example:

Transact-SQL

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

WITH clause

<index_options>: For information about the index options, see CREATE XML INDEX (Selective XML Indexes).

Best Practices

Create a selective XML index instead of an ordinary XML index in most cases for better performance and more efficient storage. However, a selective XML index is not recommended when either of the following conditions is true:

You need to map a large number of node paths.
You need to support queries for unknown elements or elements in an unknown location.

Limitations and Restrictions

For information about limitations and restrictions, see Selective XML Indexes (SXI).

Security

Permissions

Requires ALTER permission on the table or view. User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles.

Examples

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.

Transact-SQL

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

The following example includes a WITH XMLNAMESPACES clause.

Transact-SQL

CREATE SELECTIVE XML INDEX on T1(C1)
WITH XMLNAMESPACES ('http://www.tempuri.org/' as myns)
FOR ( path1 = '/myns:book/myns:author/text()' )

Concepts

Selective XML Indexes (SXI)

Create, Alter, and Drop Selective XML Indexes

Specify Paths and Optimization Hints for Selective XML Indexes

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD