Creating XML Schema in SQL Server 2005

In this article let us see a very basic example of XML schema and its usage. In order to validate an XML file create a XSD for it. XSD allows you to define a full structure for a XML document that must be followed to be validated. If we enter a different structure it would result in an error. Let us create a sample table structure, schema and test it by inserting couple of records into it.

Create a XML Schema

Create xml schema collection TestTableSchema
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="TestTable">
<xsd:complexType mixed="false">
<xsd:sequence>
<xsd:element name="FirstName" type="xsd:string"/>
<xsd:element name="Age" type="xsd:integer"/>
<xsd:element name="Sex" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>';

This above schema defines the TestTable XML. It defines it to contain three elements:

1. FirstName,
2. Age and
3. Sex with corresponding datatypes. Let us attach the above scheme to a field while creating our test table.

Attach the XML Schema

Create table TestTable
(
Sno int identity primary key,
OtherDetails XML(TestTableSchema) not null
)

Test our Code

The below sample record would insert properly after the validation.

Insert into TestTable
Values (
'<TestTable>
<FirstName>Vadivel</FirstName>
<Age>29</Age>
<Sex>M</Sex>
</TestTable>')

The below sample code snippet would fail because we have introduced a new element which was not there in the XSD "salary".

Insert into TestTable
Values (
'<TestTable>
<FirstName>VadivelDeception Point</FirstName>
<Age>29</Age>
<Sex>M</Sex>
<Salary>1000</Salary>
</TestTable>')

It would throw an error message as folows:

Msg 6923, Level 16, State 1, Line 1
XML Validation: Unexpected element(s) at the end of content model: Salary

Technorati tags: SQL, Databases, SQL Server, SQL Server 2005