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 2

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.

Relational Storage: Still the Default
Figure 2. SQL Server 2005: SQL Server 2005 lets you expose natively stored XML in a tabular format.
The relational database isn't very sexy, but even after 25 years of commercial availability, it's still the best way to store structured data. Structured data has at least two characteristics:
  • Dense—Every entity has roughly the same set of known properties.
  • Regular—Entities can reference each other—think foreign keys—but in a predictable manner.
Here's a more intuitive test: If your data can easily fit into a spreadsheet, it's probably structured. Of course, the concept of structure in data exists on a continuum, so extremes are relatively rare. Even contact information can be sparsely populated—not everyone has a first name, a last name, and a middle initial, so these fields are occasionally NULL—but relational is usually the best way to store it. Here's some contact information in a tabular format:

Email GivenName FamilyName Street Unit City State PostalCode Country
Cher@Example.com NULL Cher 1234 American Way 15d YourCity CA 54321 USA
Bpilgrim@example.com Billy Pilgrim 5432 American Way NULL AnyTown CO 12345 USA

Here's the XML equivalent:

     <!--No family/given name pair--> 
          <Street>123 American Way</Street>

          <Street>543 American Way</Street>
Each representation has its advantages: The table is more succinct; XML is better at dealing with Cher's single name. But overall, the table is the best way to represent this kind of data for a variety of reasons:
  • Query performance—The relational model is much simpler than XML, which makes searching easier and faster.
  • Storage space—XML is notoriously space-inefficient—maybe not a problem for a config file, but size can be an issue for large databases.
  • Locking—The XML type is lockable like any other type. So, if many users are updating the same set of documents, they can run into decreasing performance and even deadlocks.
  • Legacy—A lot of technological infrastructure supports the relational model, such as reporting software and business intelligence systems.
So even if you import data in an XML format, it's typically best to shred it up and store it in a relational format. And this is where SQL Server 2000 is already pretty good. Listing 1 shows a XML file, shredded using SQL Server 2000's OPENXML rowset. Listing 2 shows a way to publish XML query results using functionality new in SQL Server 2005.

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