SQL Server introduced the XML data type in 2005 as a native storage for XML documents. Because it is truly XML aware, it validates any XML saved to make sure it is correctly formed. It has additional benefits if you provide a schema, assuring that the xml is not only well formed, but conforms to the specified schema. Additionally, the XML data may be indexed inside the SQL data engine by shredding the XML optimizing XML based queries.
I have been wondering about the value of the XML data type as it is implemented in SQL Server. Because of the validation of XML data when using this data type, the performance is noticably slower than simply saving to a file or BLOB data type in SQL Server. All of the validation may be performed before sending the XML to SQL Server, so why have your data engine carry the load?
Additionally, I have found performance is increased if you segregate the XML data on a separate file object which requires extra work and more granular normalization many times.
If you are using a schema with your XML, then your data structure is well known. In that case, what is the reason for putting structured data into a relational database in a non-relational form? If all you want to do is to not have to transform it, then why even use a relational database in the first place?
Although you can combine SQL relational queries and XPath or XQueries joining relational and XML data, is this a common usage? Would this not be better done in a service layer using Linq or some other technique?
Are you using the XML Data type effectively in SQL Server? If so, please share a comment with your experience, or drop an Email to btaylor@sswug.org.
Cheers,
Ben