Database XML Data in SQL Server "Yukon"

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 ('Mike Gunderloy
Mastering SQL Server 2000Sybex')
INSERT INTO Books (BookDetails)
  VALUES ('Mike Gunderloy
MCAD 70-306 Exam CramSybex')
INSERT INTO Books (BookDetails)
  VALUES ('Mike Gunderloy
MCAD 70-305 Training GuideQue')
INSERT INTO Books (BookDetails)
  VALUES ('Mike Gunderloy
ADO and ADO.NET ProgrammingSybex')

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 CAST or 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 SELECT statement
if you just want to get the entire contents of an XML column back:


SELECT * FROM Books WHERE BookID = 1

BookID      BookDetails
----------- --------------------------------------------------------
1           Mike Gunderloy
Mastering SQL Server 2000Sybex

(1 row(s) affected)

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 <author>
tags from the Books table:


SELECT BookDetails::query('book/author') FROM Books

-------------------------------
Mike Gunderloy
Mike Gunderloy
Mike Gunderloy
Mike Gunderloy

(4 row(s) affected)

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

The 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
associated tags

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
insert, delete, and 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 method:


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 UPDATE
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.

Latest Posts

Related Stories