Three Undocumented SQL Server 2014 XML Stored Procedures
SQL Server 2014 supports the following three undocumented XML system stored procedures:
- sp_syscollector_validate_xml
- sp_xml_schema_rowset
- sp_xml_schema_rowset2
sp_syscollector_validate_xml
The sp_syscollector_validate_xml system stored procedure is used to validate the XML collector.
Syntax
sp_syscollector_validate_xml [ @collector_type_uid = ] ‘collector_type_uid’,
[ @name = ] ‘name’,
[ @parameters = ] ‘parameters’
Arguments
[ @collector_type_uid = ] ‘collector_type_uid’
Is the GUID for the collector type. collector_type_uid is uniqueidentifier,
with a default of NULL.
[ @name = ] ‘name’
Is the name of the collector type. name is sysname, with a default of NULL.
At least one of the @collector_type_uid or @name parameters must be specified.
[ @parameters = ] ‘parameters’
The XML text to validate. parameters is XML type.
Return Code Values
1 (failure).
Result Sets
None.
Remarks
The sp_syscollector_validate_xml system stored procedure exists in the msdb database.
Permissions
Execute permissions default to the dbowner role.
Example
USE msdb
DECLARE @params xml =
CONVERT(xml, N’
sp_xml_schema_rowset
The sp_xml_schema_rowset system stored procedure is used to retrieve XML schema information
for the given collection name.
Syntax
sp_xml_schema_rowset [ @collection_name = ] ‘collection_name’,
[ @schema_name = ] ‘schema_name’,
[ @target_namespace = ] ‘target_namespace’
Arguments
[ @collection_name = ] ‘collection_name’
The name of XML schema collection. collection_name is sysname, with no default.
[ @schema_name = ] ‘schema_name’
The name of a schema in which an XML schema collection is defined. schema_name is sysname,
with a default of NULL.
[ @target_namespace = ] ‘target_namespace’
The target name space of an XML schema. target_namespace is sysname, with a default of NULL.
Return Code Values
None.
Result Sets
| Column name | Type | Description |
| SCHEMACOLLECTION_CATALOGNAME | DBTYPE_WSTR | The catalog an XML collection belongs to |
| SCHEMACOLLECTION_SCHEMANAME | DBTYPE_WSTR | The schema an XML collection belongs to |
| SCHEMACOLLECTIONNAME | DBTYPE_WSTR | The name of an XML schema collection for typed XML columns, NULL otherwise |
| TARGETNAMESPACEURI | DBTYPE_WSTR | The target name space of an XML schema |
| SCHEMACONTENT | DBTYPE_WSTR | The XML schema content |
Remarks
The sp_xml_schema_rowset system stored procedure exists in the master database.
Permissions
Execute permissions default to members of the public role.
Example
This is the example to retrieve XML schema information for the ‘test’ collection:
EXEC sp_xml_schema_rowset @collection_name = ‘test’
sp_xml_schema_rowset2
The sp_xml_schema_rowset2 system stored procedure is used to retrieve XML schema information
for all collections.
Syntax
sp_xml_schema_rowset2 [ @schema_name = ] ‘schema_name’,
[ @target_namespace = ] ‘target_namespace’
Arguments
[ @schema_name = ] ‘schema_name’
The name of a schema in which an XML schema collection is defined. schema_name is sysname,
with a default of NULL.
[ @target_namespace = ] ‘target_namespace’
The target name space of an XML schema. target_namespace is sysname, with a default of NULL.
Return Code Values
None.
Result Sets
| Column name | Type | Description |
| SCHEMACOLLECTION_CATALOGNAME | DBTYPE_WSTR | The catalog an XML collection belongs to |
| SCHEMACOLLECTION_SCHEMANAME | DBTYPE_WSTR | The schema an XML collection belongs to |
| SCHEMACOLLECTIONNAME | DBTYPE_WSTR | The name of an XML schema collection for typed XML columns, NULL otherwise |
| TARGETNAMESPACEURI | DBTYPE_WSTR | The target name space of an XML schema |
| SCHEMACONTENT | DBTYPE_WSTR | The XML schema content |
Remarks
The sp_xml_schema_rowset2 system stored procedure exists in the master database.
Permissions
Execute permissions default to members of the public role.
Example
This is the example to retrieve XML schema information for all collections:
EXEC sp_xml_schema_rowset2