Home > Articles > Web Services > XML

Storing XML Data in a Relational Database

  • Print
  • + Share This
Unless you work in a highly rigid, well-structured environment, you've probably had to store objects with widely different descriptions (properties, attributes, fields, or whatever you call them) into the same relational database table. Ivan Pepelnjak describes how you can use the familiar XML markup to extend the capabilities of your relational database.
Like this article? We recommend

Unless you work in a highly rigid, well-structured environment, you’ve probably had situations in which you have to store objects with widely different descriptions (properties, attributes, fields... whatever you call them) into the same relational database table.

A very common example is an on-line department store, in which you care about the color of some products, the weight of some other products, and the sizes of a third subset of products (not to mention fabrics, expiration dates, mileage per gallon, number of pages...).

Quite commonly, ad hoc database users would define a single table with a very large number of columns, most of them being NULL most of the time.

Database designers with more experience in database normalization would quickly recognize that you need three tables to represent the same requirements (Listing 1).

  • The PRODUCTS table that would hold the basic product information
  • The ATTRIBUTES table that would hold the attribute names (weight, color, size, and so on)
  • The ATTRIBUTEVALUES table that would link the two and specify the known attributes of a product

Listing 1 Normalized product-attribute tables

CREATE TABLE Products (
 ProductID  INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
 ProductName VARCHAR(50) NOT NULL)

CREATE TABLE Attributes (
 AttributeID  INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
 AttributeName VARCHAR(50) NOT NULL)

CREATE TABLE AttributeValues(
 ProductID   INT,
 AttributeID  INT,
 AttributeValue VARCHAR(500),
 CONSTRAINT [AttributeValues_Primary] PRIMARY KEY 
  CLUSTERED (ProductID,AttributeID)
)

ALTER TABLE AttributeValues ADD CONSTRAINT Values_ProductID 
 FOREIGN KEY(ProductID) REFERENCES Products(ProductID)
 ON UPDATE CASCADE ON DELETE CASCADE

ALTER TABLE AttributeValues ADD CONSTRAINT Attributes_ProductID 
 FOREIGN KEY(AttributeID) REFERENCES Attributes(AttributeID)
 ON UPDATE CASCADE ON DELETE CASCADE

With the widespread acceptance of XML, we are commonly considering a third solution: representing rarely used object properties in an XML-formatted string that is stored in the same table as the rest of the data.

You might also view the XML data as yet another Binary Large Object (BLOB) that has to be stored in your database. For example, the Frequently Asked Questions database (described in my "Serve SQL Data in XML Format" and "Introduction to Hijax" articles) stores HTML markup in a database field. That’s a perfectly legal requirement that will not hurt you as long as you’re not trying to extract individual values from the XML data in SQL queries.

A more troublesome approach is to misuse the XML format to represent hierarchical data structure within a relational table. Don’t do it; this battle has been fought years ago and the relational model is clearly won.

  • + Share This
  • 🔖 Save To Your Account