• Article

DDL for Managing XML Schema Collections in the Database

As described in the Managing XML Schema Collections on the Server topic, you can create XML schema collections in the database and associate them with variables and columns of xml type. To manage schema collections in the database, SQL Server provides the following DDL statements:

To use an XML schema collection and the schemas it contains, you must first create the collection and the schemas by using the CREATE XML SCHEMA COLLECTION statement. After the schema collection is created, you can then create variables and columns of xml type and associate the schema collection with them. Note that after a schema collection is created, various schema components are stored in the metadata. You can also use the ALTER XML SCHEMA COLLECTION to add more components to the existing schemas or add new schemas to an existing collection.

To drop the schema collection, use the DROP XML SCHEMA COLLECTION statement. This drops all schemas that are contained in the collection and removes the collection object. Note that before you can drop a schema collection, the conditions described in DROP XML SCHEMA COLLECTION (Transact-SQL)must be met.

Understanding Schema Components

When you use the CREATE XML SCHEMA COLLECTION statement, various schema components are imported into the database. Schema components include schema elements, attributes, and type definitions. When you use the DROP XML SCHEMA COLLECTION statement, you remove the complete collection.

CREATE XML SCHEMA COLLECTION saves the schema components into various system tables.

For example, consider the following schema:

<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            targetNamespace="uri:Cust_Orders2"
            xmlns="uri:Cust_Orders2" >
  <xsd:attribute name="SomeAttribute" type="xsd:int" />
  <xsd:complexType name="SomeType" />
  <xsd:complexType name="OrderType" >
    <xsd:sequence>
      <xsd:element name="OrderDate" type="xsd:date" />
      <xsd:element name="RequiredDate" type="xsd:date" />
      <xsd:element name="ShippedDate" type="xsd:date" />
    </xsd:sequence>
    <xsd:attribute name="OrderID" type="xsd:ID" />
    <xsd:attribute name="CustomerID"  />
    <xsd:attribute name="EmployeeID"  />
  </xsd:complexType>
  <xsd:complexType name="CustomerType" >
     <xsd:sequence>
        <xsd:element name="Order" type="OrderType"
                     maxOccurs="unbounded" />
       </xsd:sequence>
      <xsd:attribute name="CustomerID" type="xsd:string" />
      <xsd:attribute name="OrderIDList" type="xsd:IDREFS" />
  </xsd:complexType>
  <xsd:element name="Customer" type="CustomerType" />
</xsd:schema>

The previous schema shows the different types of components that can be stored in the database. These include SomeAttribute, SomeType, OrderType, CustomerType, Customer, Order, CustomerID, OrderID, OrderDate, RequiredDate, and ShippedDate.

Component Categories

The Schema components stored in the database fall into the following categories:

  • ELEMENT

  • ATTRIBUTE

  • TYPE (for simple or complex types)

  • ATTRIBUTEGROUP

  • MODELGROUP

For example:

  • SomeAttribute is an ATTRIBUTE component.

  • SomeType, OrderType, and CustomerType are TYPE components.

  • Customer is an ELEMENT component.

When you import a schema into the database, SQL Server does not store the schema itself. Instead, SQL Server stores the various individual components. That is, the <Schema> tag is not stored, only the components that are defined within it are preserved. All schema elements are not preserved. If the <Schema> tag contains attributes that specify default behavior of its components, these attributes are moved to the schema components within it during the import process, as shown in the following table.

Attribute name

Behavior

attributeFormDefault

The form attribute applied to all attribute declarations in the schema where it is not already present and the value is set to the value of the attributeFormDefault attribute.

elementFormDefault

The form attribute applied to all element declarations in the schema where it is not already present and the value is set to the value of the elementFormDefault attribute.

blockDefault

The block attribute applied to all element declarations and type definitions where it is not already present and the value is set to the value of the blockDefault attribute.

finalDefault

The final attribute applied to all element declarations and type definitions where it is not already present and the value is set to the value of the finalDefault attribute.

targetNamespace

Information about the components that belong to the target namespace is stored in the metadata.