sp_db_selective_xml_index (Transact-SQL)

Article
08/10/2023

Applies to: SQL Server

Enables and disables Selective XML Index functionality on a SQL Server database. If called without any parameters, the stored procedure returns 1 if the Selective XML Index is enabled on a particular database.

Note

Starting with SQL Server 2014 (12.x), the Selective XML Index functionality cannot be disabled. This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. In SQL Server 2012 (11.x), in order to disable the Selective XML Index feature using this stored procedure, the database must be put in the SIMPLE recovery model by using the ALTER DATABASE SET Options (Transact-SQL) command.

Transact-SQL syntax conventions

Syntax

  
      sys.sp_db_selective_xml_index[[ @dbname = ] 'dbname'],   
[[ @selective_xml_index = ] 'selective_xml_index']

Arguments

[ @ dbname = ] 'dbname' The name of the database to enable or disable Selective XML Index on. If dbname is NULL, the current database is assumed. @dbname is sysname.

[ @selective_xml_index = ] 'selective_xml_index' Determines whether to enable or disable the index. Allowed values: 'on', 'off', 'true', 'false'. If another value except 'on', 'true', 'off', or 'false' is passed, an error will be raised. @selective_xml_index is varchar(6).

Return Code Values

1 if the Selective XML Index is enabled on a particular database, 0 if disabled.

Examples

A. Enable Selective XML Index functionality

The following example enables Selective XML Index on the current database.

EXECUTE sys.sp_db_selective_xml_index  
    @dbname = NULL  
  , @selective_xml_index = N'on';  
GO

The following example enables Selective XML Index on the AdventureWorks2022 database.

EXECUTE sys.sp_db_selective_xml_index  
    @dbname = N'AdventureWorks2022'  
  , @selective_xml_index = N'true';  
GO

B. Disable Selective XML Index functionality

The following example disables Selective XML Index on the current database.

EXECUTE sys.sp_db_selective_xml_index  
    @dbname = NULL  
  , @selective_xml_index = N'off';  
GO

The following example disables Selective XML Index on the AdventureWorks2022 database.

EXECUTE sys.sp_db_selective_xml_index  
    @dbname = N'AdventureWorks2022'  
  , @selective_xml_index = N'false';  
GO

C. Detect if Selective XML Index is enabled

The following example detects if Selective XML Index is enabled. Returns 1 if Selective XML Index is enabled.

EXECUTE sys.sp_db_selective_xml_index;  
GO