sp_db_selective_xml_index (Transact-SQL)
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
In order to disable the Selective XML Index using this stored procedure, the database must be put in simple recovery mode by using the ALTER DATABASE SET Options (Transact-SQL) command.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
sys.sp_db_selective_xml_index
[[ @db_name = ] 'db_name'],
[[ @selective_xml_index = ] 'action']
Arguments
[ @ db_name = ] 'db_name'
The name of the database to enable or disable Selective XML Index on. If db_name is NULL, the current database is assumed.[ @ selective_xml_index = ] 'action'
Determines whether to enable or disable the index. If another value except 'on', ‘true’, ‘off’, or ‘false’ is passed, an error will be raised.Allowed values: 'on', 'off', 'true', 'false'
Return Code Values
1 if the Selective XML Index is enabled on a particular database.
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
@db_name = NULL
, @selective_xml_index = N'on';
GO
The following example enables Selective XML Index on the AdventureWorks2012 database.
EXECUTE sys.sp_db_selective_xml_index
@db_name = N'AdventureWorks2012'
, @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
@db_name = NULL
, @selective_xml_index = N'off';
GO
The following example disables Selective XML Index on the AdventureWorks2012 database.
EXECUTE sys.sp_db_selective_xml_index
@db_name = N'AdventureWorks2012'
, @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