I have a need to record XML fragments in a SQL Server 2005 database for logging purposes (SOAP messages, in this case). Is there any reason to use the XML datatype over a simple varchar or nvarchar column?
I can see instances already where being able to use XPath to get into the data in the XML would be rather nice, and I haven't been able to find any real downsides in some brief research.
Are there any pitfalls that need to be watched out for or is there a better way to approach this?
Depending upon the size of xml and the ability of extracting particular information from within the xml, it can become a drawback. Did you consider storing the xml on the file system and just having the path in the database?
The only problem we have encountered is that writing queries against the data is more difficult.
In some cases we have stored data in XML and later dumped it into tables for reporting.
Here's a great forum post on the topic. In general, use XML datatypes if you forsee needing the XML manipulation and typing functionality.