XML Data Type in SQL Server 2005 - Part I

by David Hayden ( .NET Developer )

In my last article(s) on the new features in SQL Server 2005, I talked about creating HTTP Endpoints in SQL Server 2005:

Another new feature in SQL Server 2005 is the addition of the XML Data Type.

One can add XML fragments and documents to SQL Server 2000 right now using a text or ntext data type, for example. However, the new XML data type allows one to assign a schema collection to the xml column as well as use XQuery in your queries.

Here I will introduce the XML data type and the schema collection, and in Part II I will talk about XQuery.

Keeping Track of My Book Collection

I have a vast collection of technical books that I want to keep track of in SQL Server 2005. To keep it simple, I just want to create a table that includes the name of the book and the table of contents.

The Books table looks like this:

where the table of contents is the new XML data type. Rather than accepting any ol' XML data in the TableOfContents column, however, I have assigned a Schema, called BookCollection, to the column. I probably should have called the schema “TableOfContents”, but you get the idea.

Creating the XML Schema

Before you can assign the BookCollection schema to the column, however, you need to add the schema to SQL Server 2005. You use the CREATE XML SCHEMA COLLECTION command to do this as follows:

which allows me to enter my table of contents for each book like this:

<Chapters>
    <Chapter>
        <Title>Chapter 1</Title>
        <Page>1</Page>
    </Chapter>
    <Chapter>
        <Title>Chapter 2</Title>
        <Page>40</Page>
    </Chapter>
</Chapters>

Any XML that you try to enter into the TableOfContents column that does not match my schema gets an error. Sweet!

View Schemas in SQL Server 2005

We can verify the schema is in SQL Server 2005 by querying sys.XML_schema_collections as follows:

Inserting XML Data

Now that I have created my BookCollection Schema and Books Table and associated my TableOfContents XML column with the BookCollection schema, I am ready to start inserting data into the table.

Inserting a record is as simple as follows:

Conclusion

The new XML data type in SQL Server 2005 rocks. In this part I showed how to create a table with an XML column and create a schema associated with the XML column. In Part II, I will show you can query the new XML data type using XQuery.

Source: David Hayden ( .NET Developer )