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