XML datatype: use & misuse

This article on the informIt website describes how an XML data type can be used to store objects with varying attributes into a same table. The suggested solution is to use a built-in XML data type (eg. XMLType on Oracle) to store all varying attributes as XML data in one column.

The purposed benefit of this design is the flexibility it gives when dealing with new types. Typically no change is needed to the database structure and the client code stays unimpacted.

However this technique when applied carelessly can degenerate into the following issues:

1- None of the usual sql constraints (NOT NULL, UNIQUE, CHECK) applies on the data being part of the xml. Unless an xml schema is being defined and applied to the XMLType column this will usually lead to data of poor quality and make it much harder to spot bugs. Referential integrity cannot be enforced on the xml values either way.

2- Querying for fragments of the xml data can be slow. It is possible to create function-based indexes on an XMLType column, but in many cases elaborate manual fine-tuning will be required before achieving fast retrieval of the XML data.

3- New client code may have to be written to fetch and parse that XML. Although java libraries such as XStream can drastically simplify that task, this still adds to the overall complexity of the project.

4- while the points made above are mitigable to some degree, it remains that xml is *very* verbose, and likely to generate significant overheads at every stage of its lifecycle, when parsed, checked for well-formedness, stored, retrieved, transmitted over the network… When dealing with large volumes of data the sum of those overheads quickly translates into unacceptably low application response times (and disgruntled users).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s