XML on SQL Server 2005

While designing large databases, one of the most commonly done things to incorporate future additions to the data fields is, adding a number of generic fields that can later be used as additional data entry fields or add a memo field that can be used to store the same-all without having to change the database structure.

However, this leads to the data becoming unstructured over time.

SQL Server solves this by introducing a datatype into which you can store structured data directly. It's called 'XML', and it lets you store the XML data into a field that is of this type. Let's see what all you can do with having XML in your database tables. Since XML is now a full-fledged datatype in SQL Server, you can easily create a new table that contains a field having XML in it, using the SQL Server Management Studio (formerly, Enterprise Manager). However, we'll take a look at doing this using simple SQL commands instead.

Simple data actions



Let's first create a simple table that stores some data in XML format. For this, use the following:

CREATE TABLE Contacts (



ContactId INT IDENTITY PRIMARY KEY, 


Name VARCHAR(50) NOT NULL, 


OtherInfo XML NOT NULL)

As you can see, the 'OtherInfo' field is declared to be of XML datatype and you can store structured data into it. Now add some data into the table as:

INSERT INTO Contacts VALUES ('Bill Gates', 



'


1/1/1956


Melinda


1/1/1990






')

We've added more details for the contact than the data structure defined in the database actually allowed us to. 

This is done using a structured format, namely XML. To query for information and view the data stored in an XML field, issue: 

SELECT Name, OtherInfo FROM Contacts

This returns the XML information (which is shown as a simple hyperlink in the results). 

Validating the XML schema



How do you make sure that the XML itself follows a particular structure? For instance, how do you ensure that the data stored in the XML field in the above table does not contain useless information and conforms to the XML naming convention that you wish to use? To do this, you must validate the XML being entered into the field, using an XSD (XML Schema Definition). XSDs allow you to define a full structure for any XML document or information that must be followed to be validated. Entering a different structure will result in it being rejected. Here we'll see how to create an XSD for the XML field of the table. 

CREATE XML SCHEMA COLLECTION ContactSchema AS '




targetNamespace="http://www.microsoft.com/schemas/adventure-works/Contacts" 



xmlns:prod="http://www.microsoft.com/schemas/adventure-works/Contacts">




















































'

This schema defines the contact XML to contain the three elements: BirthDate, Spouse and Anniversary and defines the datatype of the information they respectively store. To attach a schema to a field, you'll need to do it using the Management Studio or while creating the table. Specify it as follows:

CREATE TABLE Contacts (



ContactId INT IDENTITY PRIMARY KEY, 


Name VARCHAR(50) NOT NULL, 


OtherInfo XML(ContactSchema) NOT NULL)

When you try to insert the data that was shown above, it will work fine. However, the following line will fail since the element was not defined.

INSERT INTO Contacts VALUES ('Bill Gates', 



'


1/1/1956


Melinda


1/1/1990


2







')

In the following issues, we'll look at more you can do with XML. Just as we got to press, Microsoft has announced the availability of free online e-learning courses for all developers. These will cover Windows Server 2003, VS.NET 2005 and SQL Server 2005 and are available at

hhtp://msdn.microsoft.com/elearning/24X7.

Vinod Unny



Enterprise InfoTech