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.
[!UWAGA]
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 "Ikona łącza do tematu")
Transact-SQL Syntax Conventions
Składnia
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