Database – What are the advantages of storing xml in a relational database

databasedesignxml

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?

Best Answer

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

Related Solutions

Database – how to compare two large sets of data using PL/SQL

If there are no security restrictions that prevent that, probably the easiest way is to create a database link, so those two databases see each other; then use the minus clause to find what is in one table but not the other.

CREATE DATABASE LINK otherdb
  CONNECT TO otheruser IDENTIFIED BY otherpwd USING 'otherdb.wherever';

SELECT mycolumn FROM mytable MINUS SELECT mycolumn FROM mytable@otherdb;

SELECT mycolumn FROM mytable@otherdb MINUS SELECT mycolumn FROM mytable;

Copying the table from one machine to the other (using 'CREATE TABLE xxx AS SELECT * FROM mytable@otherdb') and using that for the comparison might be faster, though.

Database History Table – How to Create and Use a Database History or Tracking Table

Poking holes: what if the database schema is changed at same point later in time, and a column name changes, or the column is deleted completely? Lots of database system allow this. What will happen to your "fieldName" then?

For data integrity: you must make sure that every update or delete operation will for sure update your tracking table. That is best accomplished by triggers calling a stored procedure. You should make sure only those stored procedure has writing access to your tracking table, so noone else can write wrong values.

If you can live with a db vendor specific solution: most db systems have system tables where the schema information (table names, table ids, column names etc) is stored. You can check if it is possible to set a foreign key reference to such a system table. That would allow to replace the field name by a field ID if the database supports something like this.

Actually, if you need to track whole rows of the specific table including all columns (and not just a small subset of the columns), you should consider @sarfeast's suggestion. Read this article about the drawbacks of name-value-pair models.

Best Answer

Related Solutions

Database – how to compare two large sets of data using PL/SQL

Database History Table – How to Create and Use a Database History or Tracking Table

Related Topic