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


XML Storage in SQL Server: Strategies and Fallout : Page 3

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.

The Right Tool for the Right Job
But there's a whole universe of data that doesn't map very well to rows and columns. If the relational model handles data that's dense and regular, XML takes care of just about everything else.

First, XML is very good at representing very hierarchical data. Navigating a hierarchy of arbitrary depth is a real hassle in SQL Server 2000. As an example of hierarchical data, consider the blueprint of a house. Various structural elements support each other in complex ways: The roof sits on beams, posts, and walls, each of which is supported by one or more structural elements below (see Listing 3). Users may want to run queries, but relationships between members are arbitrary so this kind of data doesn't map well to tables and columns.

XML is also gaining popularity for representing unstructured data. These are often prose documents, and markup helps to store them in a display-neutral fashion, so you can easily transform your document into HTML for a Web site or into a PDF document to send to the printers. Marking up prose can also add semantic transparency, making document searches more precise. Listing 4 shows a SQL script for querying unstructured data.

The example below stores an email message as XML. Not only are the standard to, from, and subject fields marked up, but the text of the message itself contains custom tags as well. These custom tags make searching through text a less ambiguous exercise.

<?xml version="1.0" encoding="utf-8"?>
   <Subject>Eyes on the Prize</Subject>
      Remember the <Event>California rollout </Event> happens <Deadline>Monday 12/14/2004.</Deadline>

SQL Server gives you the best of both worlds for storing hierarchical and unstructured data. Now, you can query and index documents that would previously be searchable only through full text indexing.

Implementation Details: Four more Nails
Depending on your situation, XML can also be the best choice for storing even highly structured data. Here are a few practical reasons to consider storing data in a field of type XML:

  • Repeated shredding or publishing—On-demand transformations carry a performance penalty. If you have to shred or publish the same document over and over again, consider storing it natively as XML. You can always expose it to relational consumers with an XML view.
  • Rapidly changing data structures—When modeled correctly, XML lives up to its name: It's extensible. Developers can add new pieces of data—even new hierarchies—to a schema without compromising existing software. Extensibility is an extra advantage when prototyping, or when working with rapidly changing problem domains such as bioinformatics.
  • Atomic data—Sometimes, you'll have XML data that's never consumed except as a whole. Think of this as logical atomicity—if you never access the parts individually, you might as well store it in one big chunk.
  • Debugging—Especially for new releases, it can be a good idea to tuck away a copy of your XML imports. The data may be redundant, but keeping the original makes tracking down problems a whole lot easier.
Microsoft's database support for XML has come a long way with SQL Server 2005. So consider these more as guidelines than as rules. There are enough nails out there; you don't need to look for any more.

Eric McMullen is a director at Falstaff Solutions, a Denver-based consulting house which specializes in data-centric .NET applications. Check out Falstaff's Web site at www.falstaffsolutions.com.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date