When do you opt to store XML in a relational database?

Question

Possible Duplicate:
Why would I ever choose to store and manipulate XML in a relational database?

Although this question on the surface appears to be a rehash of what's already been previously asked, I'll state up front that it is not. My question is not how to store or retrieve XML from a relational database. The question at hand is much more fundamental than that:

What type of data do you usually store in XML format in your database? What are your design decisions for doing so? Are you willing to give up the 'relational' aspect of your database by putting certain parts of your model into an XML blurb? Things such as preferences or configuration files can be stored as XML in a relational database, but should you do it?

Answer 1

My experience with storing XML in a relational database is usually storing serialized objects for either historical purposes or as part of a persistence strategy with the assumption that I will later retrieve this XML and rehydrate it into an object.

Answer 2

I tend to store xml blurbs (or other opaque data such as json serialized) when the data itself does not need to have relationships.

Answer 3

The only reason, IMHO, to put XML in a database is if you need to interact, edit, or change the data with your project. If you are simply reading and using the XML Data, just throw it in a file. But if that data is being modified or added to with the application, its better to use a DB for that and you don't really need XML at all, since the table will describe the structure of the data.

Answer 4

I use these conditions for storing XML data in a database.

1. I don't have the time to develop the relational tables (really, I sometimes start off this way just to get a prototype up and running).

2. The data is much to large and complex in structure to break into tables without considerable effort

In general I like to store relational data in my database, but imagine I have an object that represents all the data required to render a web page (I mean everything, fonts, images the lot), Its going to be incredibly complex, storing it in a table structure will serve to really only casue me maintenace issues - the structure is fluid. it would also cause me query issues - imagine the number joins I would need to do, and the time it would take.

Answer 5

Your code may want to process XML data in the following ways:

1. Only persist it in the database, do all the processing in the business layer, e.g. a queue or audit trail of SOAP messages to/from a web service. For this I would use a simple text column.
2. Persist it in the database and run occasional queries in the database layer, e.g. an XHTML document that users could search. For this I would use the xml data type in SQL Server 2005 or above, or the equivalent in other DBMSs.
3. Full-scale complex relational queries in the database layer, e.g. a blob of relational data that was merely serialised to XML and must now be stored in its proper form. This would obviously be stored in the set of tables that matched the deserialised data structure.

Answer 6

Never.

Storing data to which there is some kind of recognisable structure as XML, instead of as relational data, means abandoning the power of the relational algebra to operate on that data.

If that is your very intention, fine, but then be plain honest about it and just don't bother to use a relational DBMS at all, just dump your XML in some local/pseudolocal file.

(Observe that anything stored in a filesystem constitutes a database too, so it's not like that solution means that you are "not using a database". You're just not using one that is managed relationally, which was your very intention to boot.)