Monday, July 25, 2005

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
N'<?xml version="1.0" encoding="UTF-16"?>
<xsd:schema elementFormDefault="unqualified" attributeFormDefault="unqualified"
<xsd:element name="TestTable">
<xsd:complexType mixed="false">
<xsd:element name="FirstName" type="xsd:string"/>
<xsd:element name="Age" type="xsd:integer"/>
<xsd:element name="Sex" type="xsd:string"/>

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 (

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 (
<FirstName>VadivelDeception Point</FirstName>

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: , , ,


Prakash said...

Thanks man, ur blog was useful!!!


glusce said...

The site SQL Reports has a great SQL tutorial. Highly rcommended for people just getting started on SQL selects.

SQL Reports
URL 1:

Sql tutorial
URL 2: