sys.selective_xml_index_paths (Transact-SQL)


Updated: June 10, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2012)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Available beginning in SQL Server 2012 Service Pack 1, each row in sys.selective_xml_index_paths represents one promoted path for particular selective xml index.

If you create a selective xml index on xmlcol of table T using following statement,

FOR ( path1 = '/a/b/c' AS XQUERY 'xs:string',  
      path2 = '/a/b/d' AS XQUERY 'xs:double'  

There will be two new rows in sys.selective_xml_index_paths corresponding to the index sxi1.

Column nameData typeDescription
object_idintID of table with XML column.
index_idintUnique id of the selective xml index.
path_idintPromoted XML path id.
pathnvarchar(4000)Promoted path. For example, '/a/b/c/d/e'.
namesysnamePath name.
path_typetinyint0 = XQUERY

1 = SQL
path_type_descsysnameBased on path_type value 'XQUERY' or 'SQL'.
xml_component_idintUnique ID of the XML schema component in the database.
xquery_type_descriptionnvarchar(4000)Name of the specified xsd type.
is_xquery_type_inferredbit1 = type is inferred.
xquery_max_lengthsmallintMax length (in character of xsd type).
is_xquery_max_length_inferredbit1 = maximum length is inferred.
is_nodebit0 = node() hint not present.

1 = node() optimization hint applied.
system_type_idtinyintID of the system type of the column.
user_type_idtinyintID of the user type of the column.
max_lengthsmallintMax Length (in bytes) of the type.

-1 = Column data type is varchar(max), nvarchar(max), varbinary(max), or xml.
precisiontinyintMaximum precision of the type if it is numeric-based. Otherwise 0.
scaletinyintMaximum scale of the type if it is numeric-based. Otherwise, 0.
collation_namesysnameName of the collation of the type if it is character-based. Otherwise, NULL.
is_singletonbit0 = SINGLETON hint not present.

1 = SINGLETON optimization hint applied.

The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.

Catalog Views (Transact-SQL)
XML Schemas (XML Type System) Catalog Views (Transact-SQL)

Community Additions