What are the advantages of storing xml in a relational database?

Question

I was poking around the AdventureWorks database today and I noticed that a number of tables (HumanResources.JobCandidate and Sales.Individual for example) have a column which is storing xml data.

What I would to know is, what is the advantage of storing basically a database table row's worth of data in another table's column? Doesn't this make it difficult to query off of this information? Or is the assumption that the data won't need to be queried and just needs to be stored?

Answer 1

Because not all data needs to be stored relationally and writing code to process data you've been passed as XML for relational storage is time consuming (and very very tedious). This is particularly true when a lot of XML data is coming from systems which are throwing out large generic responses.

I've frequently seen situations where a message is received from another system and we don't care about 98% of what it contains. So we parse it to split out the 2% we do care about, store that relationally and then store the whole message in case we do need any of the remaining 98% later.

And SQL Server gives you some OK-ish tools and syntax for working with XML in T-SQL so it's not as if it's totally beyond practical reach for ad-hoc queries in the way it might be if you were storing, say, the contents of a CSV.

And that excludes the possibility that what you actually want to store is XML (for instance for support and debug purposes)...

Answer 2

If the data format is volatile and is subject to possible change, you may wish to put it together as XML and put into the database in this form thus avoiding future database schema change.

On the same tangent, if the data is supplied by some external system and consumed by it again, and they are unable to provide you with a permanent format, that's what you would do.

Doesn't this make it difficult to query off of this information?

SQL Server can query XML fields and variables. Not necessarily difficult, but more work, yes. But doable.

Answer 3

In my experience, the XML data is usually stored and rarely queried, but often extracted when necessary, usually when some other system needs an XML representation of some data that may be difficult or impossible to generate on-the-fly from relational data. The XML data might be pre-populated by some other process.

Answer 4

If you can imagine storing your data in a binary stream in a blob, then I'd imagine you can imagine storing your data in a xml format in a blob.

Of course, many things are best left in the imagination of the imaginer.

Say, electronic medical records for instance:

Since you'd most likely store the ASCII HL7 V2.x in a field in a database. You'd probably be apt to store HL7 V3.0 in a field in a database.

So the advantage is convenience.

Answer 5

A good example of storing XML is when you want to persist UI states in the database. The state of all application views are serialized and stored in the database and there is no need to query the XML. By UI state I mean, sort order of view, size of the windows etc.

Answer 6

I am currently working on a project that does this. We have data that needs to be processed multiple times, stored relationally. However, the processing is done in Java, and it's easier to work with XML there. So, we do a one-time pass through the relational data and store it as XML in a table. Then we can process that data in Java with one non-joining query rather than retrieving data every time, and process the same data over and over to our heart's content. It is much simpler and more efficient.

Answer 7

Often you get mixed data that is both XML and relational. (A fine example of this is a document store where each document can have metadata fields like title, date of creation, owner and so on.)

At this point you have to choose from three options:

1. Store everything in a relational DB.
2. Store everything in a native XML DB.
3. Store data in two separate DBs, XML in native XML and metadata in relational.

Option 3 is probably the cleanest but also the most expensive and the hardest to implement, plus you don't necessarily want distributed transactions in a not-very-big system. Option 2 isn't very good as native XML databases are usually extremely poor at handling relational data (which you're more likely to use in searches) and the technology is overall less mature than relational DB.

So that leaves you with option 1 as certainly not the best solution but maybe the least bad.

Answer 8

In my experience, using XML in a database ends up being because that's how the source of the data stores it, or you're adding it to an existing database to extend functionality in a way that won't require lots of database programming to support.

If you are going to be searching on the new data frequently it may make sense to split the XML into it's component parts instead. If not, it can be a useful way to save infrequently changed data.

Hope this helps, Jeff

Answer 9

What are the advantages of storing a tree (XML) in a list of tuples (a database table)?

There is no reason why the XML shouldn't be queriable from your DBMS using e.g. XPath or SPARQL.

As I see it, they are simply two different data structures. And there is no reason why they shouldn't be embedded into one another.

You can look up the reasons for why the JSON datatype was added in PostgreSQL. I think many of the same arguments apply. Except that with XML/XSD, even more validation is possible.