Edit

Share via

View a stored XML schema collection

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

After you import an XML schema collection by using CREATE XML SCHEMA COLLECTION, the schema components are stored in the metadata. You can use the xml_schema_namespace intrinsic function to reconstruct the XML schema collection. This function returns an xml data type instance.

For example, the following query retrieves an XML schema collection (ProductDescriptionSchemaCollection) from the production relational schema in the AdventureWorks2022 database.

SQL 
SELECT xml_schema_namespace(N'Production',N'ProductDescriptionSchemaCollection');
GO

If you want to see only one schema from the XML schema collection, you can specify XQuery against the xml type result that is returned by xml_schema_namespace.

SQL 
SELECT xml_schema_namespace(N'RelationalSchemaName',N'XmlSchemaCollectionName').query('
/xs:schema[@targetNamespace="TargetNameSpace"]
');
GO

For example, the following query retrieves product warranty and maintenance XML schema information from the ProductDescriptionSchemaCollection XML schema collection.

SQL 
SELECT xml_schema_namespace(N'Production',N'ProductDescriptionSchemaCollection').query('
/xs:schema[@targetNamespace="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"]
');
GO

You can also pass the optional target namespace as the third parameter to the xml_schema_namespace function to retrieve specific schema from the collection, as shown in the following query:

SQL 
SELECT xml_schema_namespace(N'Production',N'ProductDescriptionSchemaCollection', N'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain');
GO

When you create an XML schema collection by using CREATE XML SCHEMA COLLECTION in the database, the statement stores the schema components in the metadata. Only the schema components that SQL Server understands are stored. Any comments, annotations, or non-XSD attributes aren't stored. Therefore, the schema reconstructed by xml_schema_namespace is functionally equivalent to the original schema, but it will not necessarily look the same. For example, you won't see the same prefixes you had in the original schema. The schema returned by xml_schema_namespace uses t as the prefix for the target namespace and ns1, ns2, and so on, for other namespaces.

If you want to retain an identical copy of the XML schemas, you should save your XML schema in a file or in a database table in an xml type column.

The sys.xml_schema_collections catalog view also returns information about XML schema collections. This information includes the name of the collection, the creation date, and the owner of the collection.

See also