Using xml column in sql server with XSD enabled

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)

Scenario

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:

Customer
Id
Name
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.

image

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

INSERT INTO Customer(Id, Name, Orders)

 

VALUES (

1,

‘Joe C’,

N‘<Orders></Orders>’

);

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)

CREATE XML SCHEMA COLLECTION OrdersSchema AS

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

  <element name="Orders">

    <complexType>

      <sequence>

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

                  <complexType>

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

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

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

                  </complexType>

        </element>

      </sequence>

    </complexType>

  </element>

</schema>’

GO

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

image

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.

image

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;

image

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>’) 

Leave a Reply

Your email address will not be published. Required fields are marked *