SQL Server Performance Forum – Threads Archive

XML datatype – when to use

This is a repost from the sql 2000 forums. dineshasanka has replied and pointed me to some articles, and I am reading this at present. But is any one out there already using this datatype and how are they finding it.
Background: I work for a software development house where I am the technical dba for sql server and others. Today a developer came to me and asked me if I thought that having address information stored in an xml datatype would be a good idea.
My reaction to this was to say the least negative for the following reasons: denormalising the address to a single line would cause problems in validating components of the address object.
extracting data for sorting would involve string operations causing slow processing.
instinct leads me to believe that this is not a good idea and while it would save development time it would cause problems with trying to make use of the data later on. However I try and get our developers to ask questions – but beyond the above listed I cannot see provide more. I confess to not knowing anything about the xml datatype and its intended uses. Can anyone out there please itemise advantages or disadvantages of using the xml datatype to store an address – ie "23 nowhere street, bristol, united kingdom, dgr 435" would be store in a single column. How would you use the xml datatype?

Probably this new data type is an invitation for unexperienced developers to violate 1NF, but it’s useless to discuss this. Honestly I have yet to find some use in this new type. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Frank, Perhaps it has its use for information that cannot be truly normalized? I would assume that SQL Server now includes an XML parser, so when you try to insert/update an entry, it will automatically be checked against the appropriate XML template.

Yes, XML is a *very* big topic for MS and therefore also for SQL Server. And I’m sure there are some very good reasons for implementing XML support and the native XML data type, but at the moment it escapes me. [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

Hm, I see all those posts about problems exporting to an Excel file, and I’m thinking "why aren’t they using Access?" And now I’m thinking "why not use XML?" I agree, there seems to be little point in storing XML written out in <s>columns</s> [edit]a column[/edit], but that’s just a consequence of having the ability to handle XML as a data type. If they’ve made the read/write operations from/to XML files a bit simpler, I’m hoping we won’t see too many Excel questions on the forum in future.[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]

I would not start storing this type of data in XML. Data that fits the the relational model should be stored as it as well (in theory that is almost all information <img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> ). As you already stated how would you ensure data integrity of the data in the xml data type? By an external parser routine? Why buld something that the relational model already has built-in. How about performance? A seperate engine/function needs to parse and evaluate the content for each row in the database. Imagine a simple query as how to return all users from a specific city that has bought a Volvo, and half of that information is in an xml data type. If you have some data that you can not fit into the relational model for some reason, then it might be an option. For example you have an external program that works with XML files and you see the document itself as one entity, not the data in it.

I also wonder why anybody would choose to put data that can be normalised into an XML datatype. It seems perverse to me. I wonder if some people just do it so that they get used to the constructs and syntax.

]]>