October 20, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

XML Data in SQL Server "Yukon"

  • December 31, 2003
  • By Mike Gunderloy
  • Send Email »
  • More Articles »

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.






Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel