Would keeping an XML data inside sql table be an architectural misconception?

Question

I've got an SQL table that I use to keep product data. Some products have other attached data to them (be it: books have number of pages, cover type; movies have their time-length; etc).

I could use a separate table in SQL to keep those, keeping (name, value) pairs.

I can also just keep an XML-packed data in a single field in a table. It's not a normalized approach, but seems more-natural for me.

Answer 1

I did a similar thing in a shopping basket application. We needed to attach meta data to the products without creating too much of a schema, which would have restricted the format of the meta-data in the future. We kept the meta-data as XML.

The only reason I would not do it is if you're going to end up performing queries on the data. Just make sure you won't have some daft person wanting reports by Publisher meta-data or something (which has happened to me) and you should be fine.

Answer 2

If you were intending to use XML as a way of not properly defining database tables that would indeed an architectural cop-out. I'm not sure about your scenario, it seems dangerously close to that. But key-value pairs are probably worse.

The best thing is to use a specialist XML datatype, if your database has one. In addition to RageZ's list, Oracle as had an XMLType for ten years now (since 9i). The advantage of using XMLType is two-fold. It announces to the casual observer that the documents in this column are XML. It also gives you access to built-in functionality, such as validation with XML Schemas, should you want it. Other features could prove handy if you subsequently have to start referring to the contents of the XML. For instance, Oracle's XDB supports an XML index type which can dramatically improve the performance of XPath queries.