XML Data in SQL Server "Yukon"
If you've been working with SQL Server 2000, you're probably already familiar with the XML capabilities provided by the SQLXML technologies, a set of add-on libraries for SQL Server. With SQLXML installed, you can return the results of relational queries as XML fragments, use XPath to query your database, and so on.
Nice though they are, though, the SQLXML technologies are not a core part of SQL Server. With the next version of SQL Server, code-named "Yukon" (currently in beta), XML becomes a core part of SQL Server itself. Right out of the box, SQL Server "Yukon" contains an amazing array of features for dealing with XML. In this article I'll give you a brief overview, and then demonstrate some specific syntax. Keep in mind that we're a long way from release, and some of the syntax details may change in the interim. But whether the fine details change or not, it's clear that deep XML integration is one of the key advances in this version.
Overview of XML Data in "Yukon"
Here are some of the things you can plan on seeing in SQL Server "Yukon":
- A first-class XML data type (just like int or varchar)
- XML columns can have defaults and constraints like any other column
- Use XQuery to retrieve data from inside XML columns
- Typed XML columns whose contents must conform to a given schema
- Retrieve data from XML columns as XML or as simple data
- XQuery extensions to insert, delete, and update within XML columns
- Full-text indexing support for XML column contents
- XQuery extensions to combine regular columns with XML columns
- ADO and OLE DB extensions to support the XML data type
In short, SQL Server "Yukon" provides a rich set of tools for storing and manipulating XML in databases, without treating it as a blob of binary data or an arbitrary string
Creating a Table to Store XML
To give you a feel for how this stuff actually works, I'll build and use a
simple table with an untyped XML column (that is, an XML column that does not
have a specific schema associated with it). To create the table, I can use the
CREATE TABLE statement:
CREATE TABLE Books (BookID int identity PRIMARY KEY, BookDetails xml)
That's all there is to it! When you want XML, just use the xml data type, which is a fundamental part of the product. Inserting data is simple as well:
INSERT INTO Books (BookDetails) VALUES ('
') INSERT INTO Books (BookDetails) VALUES (' Mike Gunderloy Mastering SQL Server 2000 Sybex ') INSERT INTO Books (BookDetails) VALUES (' Mike Gunderloy MCAD 70-306 Exam Cram Sybex ') INSERT INTO Books (BookDetails) VALUES (' Mike Gunderloy MCAD 70-305 Training Guide Que ') Mike Gunderloy ADO and ADO.NET Programming Sybex
Note that although I've broken the lines to fit in this article, each XML
value is simply one long string in the
INSERT statement. You can
also use an explicit
CONVERT statement to
convert a string data type to XML, and the
OPENROWSET statement has
been enhanced to allow you to bulk load XML data from files.
Using XQuery With XML Columns
Of course, you can use a regular column-based
if you just want to get the entire contents of an XML column back:
SELECT * FROM Books WHERE BookID = 1 BookID BookDetails ----------- -------------------------------------------------------- 1
(1 row(s) affected) Mike Gunderloy Mastering SQL Server 2000 Sybex
But if that was all you wanted, you could just as well store the XML value in
an nvarchar column. The key feature that XML columns add to the picture is the
ability to query within the XML by using a standard XQuery expression.
For example, here's how you could retrieve only the
tags from the Books table:
SELECT BookDetails::query('book/author') FROM Books -------------------------------
Mike Gunderloy Mike Gunderloy Mike Gunderloy Mike Gunderloy(4 row(s) affected)
query() method returns its results as an XML fragment. You
can also use the closely-allied
value() method to return the
results as scalar values instead of untyped XML:
SELECT BookDetails::value('book/title', 'nvarchar(max)') FROM Books --------------------------- Mastering SQL Server 2000 MCAD 70-306 Exam Cram MCAD 70-305 Training Guide ADO and ADO.NET Programming (4 row(s) affected)
value() method requires you to specify the data type that
you want to get back (think of it as a sort of
CAST statement for
XQuery. Using this method returns just the contents of the XML, without the
Using XQuery DML
As defined by the W3C, the XQuery language lacks any data manipulation
language (DML) capabilities. That didn't stop Microsoft, though. They've defined
update methods that
you can use with XQuery expressions to actually manipulate the nodes within a
document inside of an XML column. For example, here's how you might use the
UPDATE Books SET BookDetails::modify('update book/author/text() to "Michael Gunderloy"')
As you can see, this is very similar to any other T-SQL
statement. The difference is in the use of the
update method to
change only part of the data within the XML column, while leaving the rest of
the column untouched. Similarly, you can insert or delete nodes from the XML by
using the appropriate methods.
Put on Your Thinking Cap
Of course, you won't be trusting your mission-critical XML data to SQL Server "Yukon" just yet; the product likely won't be released for the better part of a year. But you can certainly start planning for these capabilities as you move forward into new projects and consider updating old ones. With XML rapidly becoming the common data storage language of choice for applications programming, SQL Server "Yukon" will be well-positioned to act as a universal XML repository. Imagine capturing line of business data (CRM contacts, invoice details, purchase orders, job applications, you name it) into XML columns in SQL Server, and then being able to quickly query that data to locate XML tags of interest. I expect this to provide a fast and easy-to-program interface for many applications to take deeper advantage of XML than they currently do.
About the Author
Mike Gunderloy is the author of over 20 books and numerous articles on development topics, and the lead developer for Larkware. Check out his MCAD 70-305, MCAD 70-306, and MCAD 70-310 Training Guides from Que Publishing. When he's not writing code, Mike putters in the garden on his farm in eastern Washington state.