SQLUNINTERRUPTED

Continuing with the discussion on XML, today we will talk about XML Schema collections. For a complete list of related topics, please refer here. If you are looking for a detailed course on XML Schema Definitions, please start here.

Schema collections were introduced in SQL Server 2005, and allow the user to create a collections of XML schema definitions, which can later be used define a typed XML variable, parameter or XML column. Just like a normal table, an XML Schema collection can be created, dropped or modified. SQL Server 2005/2008 provides some guidelines and limitations for creating XML schema collections and XML schema definitions. The details can be found at the following MSDN site

SQL 2005: http://msdn.microsoft.com/en-us/library/ms190665(v=SQL.90).aspx

SQL 2008: http://msdn.microsoft.com/en-us/library/ms190665(v=SQL.100).aspx

In this article we would be taking about the DDL support for creating XML Schema collections. We will also talk about what needs to be done, if one of the schema definition (xsd), needs to be changed.

Before we get on with the DDL for creating a schema collection, lets quickly figure out where in the Database are these collections created. Since AdventureWorks database already has some SchemaCollections defined, lets have a look at these.

In the AdventureWorks database browse to Programmability->Types->XML Schema Collection, we would find of the Schema collections being used with the database.

Each, SchemaCollection mentioned in the list contains at least one XML Schema definition, which would be used by some XML column in one of the tables in the Database. Additionally these can be used with XML variables and parameters.

DDL FOR SCHEMA COLLECTIONS

SQL Server supports Creation/deletion/modification of the SCHEMA COLLECTION objects using the following T-SQL syntax.

CREATE XML SCHEMA COLLECTION [ <relational_schema>. ]sql_identifier AS Expression
DROP XML SCHEMA COLLECTION [ relational_schema. ]sql_identifier
ALTER XML SCHEMA COLLECTION [ relational_schema. ]sql_identifier ADD ‘Schema Component’

Once we have created an XML Schema Collection, we can then query the details using the following DMV’s

To list all the schema collection in the Database: sys.xml_schema_collections
To list the XML namespaces in the  database: sys.xml_schema_namespaces

Now lets try to create an XML schema collection in the Database

CREATE XML SCHEMA COLLECTION ITEMS
AS
‘<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    targetNamespace="http://TestNameSpace.com"
    xmlns:ns="http://TestNameSpace.com"
    elementFormDefault="qualified">
<xsd:element name ="Book" >
      <xsd:complexType>
        <xsd:sequence>
              <xsd:element name="Publication" type="xsd:string"/>
              <xsd:element name="AuthorFirstName" type="xsd:string" minOccurs="0" maxOccurs="1"/>
              <xsd:element name="AuthorLastName" type="xsd:string" minOccurs="0" maxOccurs="1"/>
              <xsd:element name="PublishedDate" type="xsd:string" minOccurs="0" maxOccurs="1"/>
        </xsd:sequence>
      <xsd:attribute name="BookID" type="xsd:int"/>
  </xsd:complexType>
</xsd:element>
</xsd:schema>’

Once the SchemaCollection is added to the database, we can declare typed XML columns or XML variables using this schema. For example

Declare @Var1 XML(Books)
create table BookDetails (PublicationDate Datetime, Book XML(ITEMS))

To modify the XML schema collection, we would need to take care of all the dependencies. In our example, the Book Column in Table BookDetails is currently typed against the Books Schema collection. Attempts to modify the schema (add element or attribute) will not fail, but removing an element from the schema or modifying the data type of the elements might generate an error.

— Modiying the existing schema in the Collection
ALTER XML SCHEMA COLLECTION ITEMS
ADD ‘<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    targetNamespace="http://TestNameSpace.com"
    xmlns:ns="http://TestNameSpace.com"
    elementFormDefault="qualified">
              <xsd:element name="BookType" type="xsd:string"/>
</xsd:schema>’
— Adding a new schema to the Collection
ALTER XML SCHEMA COLLECTION ITEMS
ADD ‘<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    targetNamespace="http://CarNameSpace.com"
    xmlns:ns="http://CarNameSpace.com"
    elementFormDefault="qualified">
<xsd:element name ="Car" >
      <xsd:complexType>
        <xsd:sequence>
              <xsd:element name="Company" type="xsd:string"/>
              <xsd:element name="Model" type="xsd:string" minOccurs="0" maxOccurs="1"/>
              <xsd:element name="Color" type="xsd:string" minOccurs="0" maxOccurs="1"/>
              <xsd:element name="Type" type="xsd:string" minOccurs="0" maxOccurs="1"/>
        </xsd:sequence>
      <xsd:attribute name="EngineID" type="xsd:int"/>
  </xsd:complexType>
</xsd:element>
</xsd:schema>’

When inserting or updating the XML contents in the table, the final XML would be validated against the schema declaration in the collection. If any mismatch is found, an error will be generated. In SQL 2005, there is strict type validation is performed. There are no LAX validations allowed in SQL 2005, while in SQL 2008 LAX validations of Any, AnyType and AnyAttribute is allowed. We will talk more about these in subsequent posts.

An attempt to insert an XML which does not comply to the defined schema, generated an error. For example the below mentioned insert will generate an error as indicated…

insert into BookDetails values (getdate(),
‘<Book xmlns="http://TestNameSpace.com" BookID="TEST"> </Book>’)
—- Error Message
Msg 6926, Level 16, State 1, Line 1
XML Validation: Invalid simple type value: ‘TEST’. Location: /*:Book[1]/@*:BookID

XML SchemaCollections cannot be dropped while it is in use by any of the following

1. Associated with any xml type parameter or column.
2. Specified in any table constraints.
3. Referenced in a schema-bound function or stored procedure.

Attempts to drop an SCHEMA COLLECTION while in use will generate an error.

DROP XML SCHEMA COLLECTION ITEMS
—– Error
Msg 6328, Level 16, State 1, Line 1
Specified collection ‘Items’ cannot be dropped because it is used by object ‘dbo.BookDetails’.

To Drop an existing XML SCHEMA COLLECTION, we need to perform the following steps.

1. Either convert all the columns to Well-Formed Untyped XML, or drop the Columns/Tables
2. Drop the Schema Collection
3. Recreate the SCHEMA Collections for other schemas.

In the earlier example, to remove the BOOKS schema definitions, we would have to perform the steps as follows

1. Alter table "BookDetails" to make the "Book" column as Well-Formed
    ALTER TABLE BookDetail ALTER Column Book XML
2. Drop the XML Schema collection
    DROP XML SCHEMA COLLECTION ITEMS
3. Recrete the XML Schema Collections
    Create XML Schema Collection…..

In the next post I will talking about XML Indexes in SQL Server 2005 and SQL Server 2008. Till then happy learning.