ALTER XML SCHEMA COLLECTION (Transact-SQL)
Applies to: 
SQL Server
Azure SQL Managed Instance
Adds new schema components to an existing XML schema collection.
Transact-SQL syntax conventions
Syntax
ALTER XML SCHEMA COLLECTION [ relational_schema. ]sql_identifier ADD 'Schema Component'
Arguments
relational_schema
Identifies the relational schema name. If not specified, the default relational schema is assumed.
sql_identifier
Is the SQL identifier for the XML schema collection.
' Schema Component '
Is the schema component to insert.
Remarks
Use the ALTER XML SCHEMA COLLECTION to add new XML schemas whose namespaces are not already in the XML schema collection, or add new components to existing namespaces in the collection.
The following example adds a new <element> to the existing namespace https://MySchema/test_xml_schema in the collection MyColl.
-- First create an XML schema collection.
CREATE XML SCHEMA COLLECTION MyColl AS '
<schema
xmlns="http://www.w3.org/2001/XMLSchema"
targetNamespace="https://MySchema/test_xml_schema">
<element name="root" type="string"/>
</schema>'
-- Modify the collection.
ALTER XML SCHEMA COLLECTION MyColl ADD '
<schema xmlns="http://www.w3.org/2001/XMLSchema"
targetNamespace="https://MySchema/test_xml_schema">
<element name="anotherElement" type="byte"/>
</schema>';
ALTER XML SCHEMA adds element <anotherElement> to the previously defined namespace https://MySchema/test_xml_schema.
Note that if some of the components you want to add in the collection reference components that are already in the same collection, you must use <import namespace="referenced_component_namespace" />. However, it is not valid to use the current schema namespace in <xsd:import>, and therefore components from the same target namespace as the current schema namespace are automatically imported.
To remove collections, use DROP XML SCHEMA COLLECTION (Transact-SQL).
If the schema collection already contains a lax validation wildcard or an element of type xs:anyType, adding a new global element, type, or attribute declaration to the schema collection will cause a revalidation of all the stored data that is constrained by the schema collection.
Permissions
To alter an XML SCHEMA COLLECTION requires ALTER permission on the collection.
Examples
A. Creating XML schema collection in the database
The following example creates the XML schema collection ManuInstructionsSchemaCollection. The collection has only one schema namespace.
-- Create a sample database in which to load the XML schema collection.
CREATE DATABASE SampleDB;
GO
USE SampleDB;
GO
CREATE XML SCHEMA COLLECTION ManuInstructionsSchemaCollection AS
N'<?xml version="1.0" encoding="UTF-16"?>
<xsd:schema targetNamespace="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"
xmlns ="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"
elementFormDefault="qualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:complexType name="StepType" mixed="true" >
<xsd:choice minOccurs="0" maxOccurs="unbounded" >
<xsd:element name="tool" type="xsd:string" />
<xsd:element name="material" type="xsd:string" />
<xsd:element name="blueprint" type="xsd:string" />
<xsd:element name="specs" type="xsd:string" />
<xsd:element name="diag" type="xsd:string" />
</xsd:choice>
</xsd:complexType>
<xsd:element name="root">
<xsd:complexType mixed="true">
<xsd:sequence>
<xsd:element name="Location" minOccurs="1" maxOccurs="unbounded">
<xsd:complexType mixed="true">
<xsd:sequence>
<xsd:element name="step" type="StepType" minOccurs="1" maxOccurs="unbounded" />
</xsd:sequence>
<xsd:attribute name="LocationID" type="xsd:integer" use="required"/>
<xsd:attribute name="SetupHours" type="xsd:decimal" use="optional"/>
<xsd:attribute name="MachineHours" type="xsd:decimal" use="optional"/>
<xsd:attribute name="LaborHours" type="xsd:decimal" use="optional"/>
<xsd:attribute name="LotSize" type="xsd:decimal" use="optional"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>' ;
GO
-- Verify - list of collections in the database.
SELECT *
FROM sys.xml_schema_collections;
-- Verify - list of namespaces in the database.
SELECT name
FROM sys.xml_schema_namespaces;
-- Use it. Create a typed xml variable. Note the collection name
-- that is specified.
DECLARE @x xml (ManuInstructionsSchemaCollection);
GO
--Or create a typed xml column.
CREATE TABLE T (
i int primary key,
x xml (ManuInstructionsSchemaCollection));
GO
-- Clean up.
DROP TABLE T;
GO
DROP XML SCHEMA COLLECTION ManuInstructionsSchemaCollection;
Go
USE master;
GO
DROP DATABASE SampleDB;
Alternatively, you can assign the schema collection to a variable and specify the variable in the CREATE XML SCHEMA COLLECTION statement as follows:
DECLARE @MySchemaCollection nvarchar(max);
SET @MySchemaCollection = N' copy the schema collection here';
CREATE XML SCHEMA COLLECTION AS @MySchemaCollection;
The variable in the example is of nvarchar(max) type. The variable can also be of xml data type, in which case, it is implicitly converted to a string.
For more information, see View a Stored XML Schema Collection.
You can store schema collections in an xml type column. In this case, to create XML schema collection, perform the following steps:
Retrieve the schema collection from the column by using a SELECT statement and assign it to a variable of xml type, or a varchar type.
Specify the variable name in the CREATE XML SCHEMA COLLECTION statement.
The CREATE XML SCHEMA COLLECTION stores only the schema components that SQL Server understands; everything in the XML schema is not stored in the database. Therefore, if you want the XML schema collection back exactly the way it was supplied, we recommend that you save your XML schemas in a database column or some other folder on your computer.
B. Specifying multiple schema namespaces in a schema collection
You can specify multiple XML schemas when you create an XML schema collection. For example:
CREATE XML SCHEMA COLLECTION N'
<xsd:schema>....</xsd:schema>
<xsd:schema>...</xsd:schema>';
The following example creates the XML schema collection ProductDescriptionSchemaCollection that includes two XML schema namespaces.
CREATE XML SCHEMA COLLECTION ProductDescriptionSchemaCollection AS
'<xsd:schema targetNamespace="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"
xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"
elementFormDefault="qualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:element name="Warranty" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="WarrantyPeriod" type="xsd:string" />
<xsd:element name="Description" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<xs:schema targetNamespace="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"
xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"
elementFormDefault="qualified"
xmlns:mstns="https://tempuri.org/XMLSchema.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:wm="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain" >
<xs:import
namespace="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain" />
<xs:element name="ProductDescription" type="ProductDescription" />
<xs:complexType name="ProductDescription">
<xs:sequence>
<xs:element name="Summary" type="Summary" minOccurs="0" />
</xs:sequence>
<xs:attribute name="ProductModelID" type="xs:string" />
<xs:attribute name="ProductModelName" type="xs:string" />
</xs:complexType>
<xs:complexType name="Summary" mixed="true" >
<xs:sequence>
<xs:any processContents="skip" namespace="http://www.w3.org/1999/xhtml" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
</xs:schema>'
;
GO
-- Clean up
DROP XML SCHEMA COLLECTION ProductDescriptionSchemaCollection;
GO
C. Importing a schema that does not specify a target namespace
If a schema that does not contain a targetNamespace attribute is imported in a collection, its components are associated with the empty string target namespace as shown in the following example. Note that not associating one or more schemas imported in the collection results in multiple schema components (potentially unrelated) being associated with the default empty string namespace.
-- Create a collection that contains a schema with no target namespace.
CREATE XML SCHEMA COLLECTION MySampleCollection AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema" xmlns:ns="http://ns">
<element name="e" type="dateTime"/>
</schema>';
GO
-- query will return the names of all the collections that
--contain a schema with no target namespace
SELECT sys.xml_schema_collections.name
FROM sys.xml_schema_collections
JOIN sys.xml_schema_namespaces
ON sys.xml_schema_collections.xml_collection_id =
sys.xml_schema_namespaces.xml_collection_id
WHERE sys.xml_schema_namespaces.name='';
See Also
CREATE XML SCHEMA COLLECTION (Transact-SQL)
DROP XML SCHEMA COLLECTION (Transact-SQL)
EVENTDATA (Transact-SQL)
Compare Typed XML to Untyped XML
Requirements and Limitations for XML Schema Collections on the Server