Storing data in DB field as XML (varchar field)

[Curtis Justus]

Hi,

I didn't quite know where to post this, so I figured I would start here.

I am trying to look at the pros and the cons of storing XML in a single
field of a database table. Here is a sample structure:

Table: contact
Fields:
FirstName varchar(20)
LastName varchar(20)
XML_Addresses varchar(1000)
etc...

One of the arguments for having this type of a setup is that the structure
of an Address in this context can change without having to make changes in
the database structure. For that matter, the change could take place in the
UI and just pushed through the business layer directly to the database as a
"dummy field."

I am looking for as many arguments for and against this type of data model.
I don't want to share my view in fear that objectivity might be lost.

Thanks in advance,
cj

 

[Sahil Malik]

For ---
Flexibility


Against ---
Querying within the database will be a pain. (Unless this is Sql 2k5)
Bloat - VarChar(1000) - XML with it's tags is urrghhh - (Search my blog for
XMLitis, you'll know what I mean).
DomDocument.Load isn't the mostest - efficientest thing to do.
If you indeed use the flexibility, then you can't really make any sense of
the data for querying purposes since each row might be completely different
(i.e. use Schema).
If this is a short term solution then Sql2k5 has Xml Datatype support in
it - but that too isn't as performant as regular intrinsic data types.
Given infinite flexibilityHow can you guarantee that you will always be
under 1000 characters.
And if this were indeed such a perfect approach why do we even need any
columns - wait - why do we even need a database?

Having said that, in certain situations the flexibility might still warrant
this choice.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[Curtis Justus]

Sahil,

First, your BLOG was hilarious. Thanks for forwarding me there...

Next, at the stage we are at in the project, I don't think we can assume
that 2k5 is going to be used. Even so, I would like to provide my UI with
some consistency from the business layer. If the client decides to go to
2k5 in the middle of the project, we would only have to update the business
layer's communication to the database -- the UI can remain unaffected. If
they insist on the XML fields, I suppose I could write a helper object that
would convert that to an individual dataset so we can bind to it.

Maybe that prompts another question: can you bind to an XMLDom object as a
source for grids, etc.?

Thanks again,
cj

 

[Sahil Malik]

Glad you found that funny - it makes sense though.

In an ideal world you might need to update the business layer only - but if
you do indeed intend to use the varchar xml to the extent of flexibility you
cannot possibly get through table design, you will in most cases end up
changing more than just the business layer anyway. That is just my practical
view - the ideal MVC pattern and layer seperation - works at times. There is
another relevant blog post
http://dotnetjunkies.com/WebLog/sahilmalik/archive/2004/12/30/39842.aspx
(This one is not humurous - sorry LOL).

Anyway, the fact that datasets are so easily convertible to XML, and well -
you shouldn't affect yourself with XMLitis to that extent anyway - you can
use Formatters and your own serialization mechanisms to do almost anything -
I try very hard to try and not save XML as varchar into the DB, though there
have been instances where I have done that myself.

This is again a very very subjective thing and I am sure a lot of
intelligent people here will disagree or present alternate views - which is
all good, but if I had to sum up my feelings about throwing data as XML into
the db .. I'd say .....

Try hard not to do it, but don't put yourself in a hospital trying to avoid
it.

Maybe that prompts another question: can you bind to an XMLDom object as a
source for grids, etc.?

<--- That should be acheivable. At the very least there is DataSet.ReadXml.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik