RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


XML Storage in SQL Server: Strategies and Fallout

Native XML support is one of the most powerful features included with SQL Server 2005. Read these best practices to make sure you don't overuse it.

liché but true: To someone with a hammer, everything looks like a nail. We developers seem to be especially vulnerable to this problem because we like technology for its own sake. We'll get a whole bagful of hammers when SQL Server 2005 (formerly codenamed Yukon) comes out this year, and one of the most important is native XML support.

Increasingly, developers don't have a choice between relational and XML data. Each has its strengths and each has its place. Reports and business intelligence applications work primarily with relational data, but venders and customers need data in XML. And both kinds of data are increasingly integrated: change requests refer to customer email; trouble-tickets reference white papers. SQL Server 2005 can help you manage all these different kinds of data under a single platform.

What's new?
Even without any built-in support for XML, a database system typically offers more robust management functionality than a file system. Features such as security, backup, and mirroring make the database a good home for documents.

But XML isn't completely new to the Microsoft database world: SQL Server 2000 shipped with some XML functionality, and SQLXML continues to improve client-side functionality. But most of the XML support in that version has to do with mapping between relational and markup data. The OPENXML keyword is used to shred documents into a tabular format for storage. And the FOR XML clause can be used to publish relational data as XML on demand.

What You Need
To really follow along, you'll need SQL Server 2005. MSDN subscribers can download beta 2 from MSDN. Everyone else can get the Express Edition from Microsoft's SQL Server Developers Center.
Figure 1. SQL Server 2000: SQLXML and T-SQL language enhancements enable mapping between markup and tabular data but offer little support for working with XML natively.

It's a nice piece of indirection: Developers don't (necessarily) have to learn database libraries and DBAs don't have to worry (too much) about the intricacies of XML. But the indirection is only one-way: SQL Server 2000 offers very little in the way of native XML support. You can store your document in a string, but you can't index it, or shred it on the fly—not without a lot custom development (see Figure 1).

SQL Server 2005 adds symmetry to the storage-type indirection: Now that XML is built-in, you can manage XML data with database tools such as indexes, constraints, and queries. Even better, you can use XML views to expose markup data as rows and columns (see Figure 2).

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date