Changing XML Schema attached in SQL Server 2005

XML has become an first class datatype in SQL Server 2005 . You can check my previous articles for an idea of XML features introduced. In this article we will take a look at how we can alter a XML Schema attached to an Typed XML data. XML's can be caegorized into two categories, typed and un-typed. Any XML that conforms to an XML schema is called as typed. And on the contrary plain XML strings are called as un-typed.

Creating Schema

The schema for our example would be to include books schema XML which includes name, author, publisher, cost and comments section. A typical schema would look as below. We would use the CREATE XML SCHEMA COLLECTION DDL to create this schema definition.

CREATE XML SCHEMA COLLECTION BooksSchemaCollection AS

N'<?xml version="1.0" encoding="UTF-16"?>

<xsd:schema elementFormDefault="unqualified"

   attributeFormDefault="unqualified"

   xmlns:xsd="http://www.w3.org/2001/XMLSchema" >

    <xsd:element name="book">

                        <xsd:complexType mixed="false">

                                    <xsd:sequence>

                                                <xsd:element name="name" type="xsd:string"/>

                                                <xsd:element name="author" type="xsd:string"/>

                                                <xsd:element name="publisher" type="xsd:string"/>

                                                <xsd:element name="cost" type="xsd:integer"/>

                                                <xsd:element name="comments" type="xsd:string"/>

                                    </xsd:sequence>                      

                        </xsd:complexType>

    </xsd:element>

</xsd:schema>';  

Creating Table

Now with out schema definition ready we are all set to bind the same to our table. A typical XML datatype inlcuded table would look like below

I would next insert a sample data into this table that conforms to the schema definition. If there is an error while inserting we get an error stating:

Msg 6965, Level 16, State 1, Line 1

XML Validation: Invalid content,expected element(s)

We can insert a successful record as:

Changing the Schema Definition

We cannot change the schema definition easily here as this schema is bound to the table. Hence we need to unbind this and then chane our schema definition as:

Alter table XMLTable Alter Column xDoc XML

Now we are all set to change the schema definition. For this example we will add another node to the XML schema that will carry the Discount value. Hence the schema will be changed finally as:

Before we can apply this new schema to our XMLTable we must take care to update our tables XML data such that the schema definition is NOT violated. Hence for our sample reference we need to insert an discount node into our tables data like:

Update XMLTable

SET xDoc.modify('insert <discount>.10</discount> after (/book/discount)[1]')

Where id = 1

Next we are all set to apply our new schema to the table using the ALTER Table syntax as we did above:

Alter table XMLTable Alter Column xDoc XML(BooksSchemaCollection)

Conclusion

XML as we said is an first class datatype. In this article we see an practical example of using XML datatype and how we can strongly type the XML datatype and how we can manipulate the XML Schema associated. Moreover, another workaround this would be to create a new schema collection and use the ALTER TABLE to retype the tables. You might also look at ALTER SCHEMA syntax for modifying the schema collection such that we can skip re-validation. I leave it to you for further exploration. As I conclude, here are the system tables that allow us to find all the namespaces and schema collections we have used in the current database.

select * from sys.xml_schema_collections

select name from sys.xml_namespaces