Jean Paul's Blog

There are 2 types of People in the World, One who Likes SharePoint and..

  • Microsoft MVP

  • MindCracker MVP

  • CodeProject MVP

  • eBook on SharePoint 2010

  • eBook on Design Patterns

  • eBook on Windows Azure

  • NLayers Framework @ CodePlex

  • MSDN Forums

  • .Net vs. Java

    Due to Public Demand
  • Advertisements

Posts Tagged ‘sql server’

Using xml column in sql server with XSD enabled

Posted by Paul on October 18, 2010

For a long time I was wondering about why the xml type is needed in sql server.  Following are a few number of advantages on using xml column types which I found implemented in industry.

  • We can store entire xml content in the column and later query on the xml tags
  • We can store application properties like Company Name, Address etc. as xml tags and with the advantage of extensibility
  • We can reduce the number of relational tables with the power of extensibility on adding new xml elements

In this article I am trying to show how xml data types can be used in sql server along with xsd (xml schema definition)


We need to create a Customer table having Id and Name of the customer and the order created by him.  An order contains multiple items and at a time the customer is having only one order.

The default implementation would look like:

Customer OrderHeader OrderDetail
Id Id Id
Name OrderDate Item
  CustomerId Quantity


We can implement the same using xml type as:

Orders (XML data type)


Step1: Creating the table

You can create the table using Sql Server Management Studio.  The Sql Server version which I am using is 2005.


Once created the table like above, you can try inserting data into it.

INSERT INTO Customer(Id, Name, Orders)




‘Joe C’,



If you are able to execute the query successfully.  Congratulations!  That seems to be a valid the table creation.

Step2: Create the XSD

The above table design is good to proceed but not the best!  The user can store any xml in the column that would be difficult for us to parse back.  So we need to ensure that only the right format of xml can be stored in the column.  We can achieve the above using XSD enabling.

The XSD is an xml format definition which says what should be the xml data stored in.  If an attempt to save an invalid format the database will throw exception.

For creating an xsd the following sql can be used. (Please remember to delete the previous records from the table)


<schema xmlns=""&gt;

  <element name="Orders">



        <element name="Order" minOccurs="0">


                        <attribute name="Id" type="int" use="required" />

                        <attribute name="Item" type="int" use="required" />

                        <attribute name="Quantity" type="float" use="required" />








After running the query successfully, you can ensure the schema was created in sql server by expanding the Programmability tree.


Step3: Set the XSD to our xml column

Open the Customer table in modify mode and select the ‘Orders’ column and se the ‘OrdersSchema’ to the Schema Collection property of it.


Save the table so that now onwards the XSD validation is enabled.

Testing the Table

You can test the above table by using following query:

DELETE FROM customer


INSERT INTO Customer(Id, Name, Orders) VALUES (1, ‘Joe C’,N‘<Orders><Order Id="1" Item="Laptop" Quantity="10"></Order></Orders>’);

INSERT INTO Customer(Id, Name, Orders) VALUES (2, ‘Dane K’,N‘<Orders><Order Id="2" Item="Pen Drive" Quantity="500"></Order></Orders>’);

INSERT INTO Customer(Id, Name, Orders) VALUES (3, ‘Mark V’,N‘<Orders><Order Id="3" Item="Mobile Phone" Quantity="30"></Order></Orders>’);


If you get the following results, then You are good.

(3 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

You can see the existing records by the query:

SELECT * FROM Customer;


Testing with a wrong Xml

You can check whether the xml schema validation is working by trying the following query which contains an “InvalidAttribute”

INSERT INTO Customer(Id, Name, Orders) VALUES (100, ‘Joe C’,N‘<Orders><Order InvalidAttribute="Value" Id="1" Item="Laptop" Quantity="10"></Order></Orders>’) 


Posted in C# | Tagged: , , , | Leave a Comment »