Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
add comment

12 Answers

up vote 24 down vote accepted

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)...

share|improve this answer
6  
+1, "eat some now, save some for later." Which was a miserable marketing campaign for candy, but it works in this case for XML storage. –  Yar Jan 19 '11 at 15:28
add comment

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.

share|improve this answer
    
+1 for decoupling data from database schema. Also you may want to explicitly mention XPath querying. –  Gary Rowe Jan 19 '11 at 15:25
    
I think you just did. :) –  user8685 Jan 19 '11 at 15:34
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

Document-oriented datastores (aka NoSql) are very popular these days:

http://en.wikipedia.org/wiki/Document-oriented_database

There's no reason you can't employ a document-oriented scheme in a relational database. You might not get all the same benefits compared to something like Mongo, but you won't have the drawbacks, either.

For a long time, if you wanted to use document-oriented storage, your only choice was shoving structured data (like XML) into a big column. The relational databases have been adding features like indexing and matching to support that.

Contrast that with Mongo, where they only thing in the database is documents. But that's another topic.

EDIT: the core idea of document-oriented is: you pull the data out, manipulate it, and shove it back in whole. Sometimes, like when you're transmitting the document to the client, you just want to send the whole thing as a blob and let them deal with it. The benefit (and drawback) is flexibility. Validation and correctness of the document is done outside the database.

EDIT EDIT: Another contrast. Imagine saving JPG images, or Word docs in a database column.

share|improve this answer
add comment

Well, XML (or JSON) is pretty good to store metadatas with hierarchy. What are the alternatives ? A metadata table with refid/key/value/depth maybe ? It's a bit cumbersome (but probably better for querying if you need to do it). Storing some xml data about a document (a row in a documents table) is pretty convenient when you want to store some hierarchical infos without having to rely on an external table or having to add 1 column per "type" of infos.

share|improve this answer
1  
this does not seem to add anything substantial over what was already posted in prior 11 answers –  gnat Mar 5 at 13:05
add comment

I'd say it was bad practice as you're clogging up otherwise efficient storage with inefficient tags which do not need to be there if you take the effort to parse out the information. XML has a hideous storage overhead compared to the data it describes, as you need one tag for each column for each row. By comparison, data parsed out and stored in relational format has its column name stored ONCE. For a dozen rows on a dev. box, big deal, but I've seen developers make the assumption this is scalable to millions of rows. This can represent 100's of GB of overhead for a few dozen GB of data, which creates operational challenges. You're basically abdicating responsibility from yourself and pushing onto the people who have to support the crap you've written.

So, why not store it AWAY from the operational data, in its own database? Or as it's intended - in flat files? It'll probably never be looked at again, so why not remove it from hitting the performance of an operational system? Remember that XML is ONLY there to provide a description of the schema of data that would otherwise not be apparent due to storage protocol differences between systems. That is its entire point, there's nothing clever about it. Storing 10x the amount of overhead for a given amount of data just says you're a sloppy developer who's not thought things through and can't be arsed to process the data you're consuming into a sensible, efficient, fast to query format. Stop pushing your effort onto operational support, and THINK about how you can better handle the data after you've received it would be my call. There's no defence for storing data as XML after it's received, as it's served its purpose.

share|improve this answer
1  
But you assume here that the data in the XML fragment is relational data. This is not generally the case – XML is very useful for hierarchical data, which is very awkward to represent in a relational DB. An idiomatic XML document (e.g. making good use of attributes) will also have fairly little space overhead, the main problem would be the cost of parsing the fragment at each access. –  amon Mar 5 at 8:45
    
The data may not be processable into a fast to query format (nor might you need to query it). Imagine an XML schema where there are hundreds of optional fields of which maybe a handful are ever populated at once. If you insist on modelling this relationally then you'll either end up with vast tables stuffed full of NULLs or the monstrosity that is EAV. –  Julia Hayward Mar 5 at 12:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.