When should I use the XML data type in SQL Server?

Question

When should I use the XML data type in Microsoft SQL Server?

Answer 1

Personally, I've never used the XML field type in SQL Server before, and I've done a lot of database programming since they added this feature. There always seemed to me to be a programmatic and performance overhead to using XML content in a database natively. I honestly thought it was a gimmick, because everyone was on the XML train at one point in the early 2000's. "Oh XML is hot, so let's add it to SQL Server so we don't look passe."

The best business case I can see might be in recording XML-based data messages you've received where you might want to peek in their structure and data, but want maintain integrity of original message for business or legal reasons. The overhead might be too great to translate the XML into a table structure, but using the XML capabilities in SQL Server might just reduce the cost of examining the data enough to warrant using it.

There's probably dozens of other reasons why you'd want to use it, but frankly I think you should consider other paths to happiness before going hog-wild with XML in SQL Server unless you have very specific business reasons for doing so. Use a varchar(max) or text field if it makes more sense.

Just my opinion, of course :)

Answer 2

I've only encountered a few scenarios where I would actively pursue the use of the XML data type in SQL Server. This is off the top of my head, in order from least to most interesting:

• Storing/archiving XML responses generated by other applications
  • For example, we use Application Integration Framework (AIF) for communicating between a custom Silverlight application and Dynamics AX. We store both inbound and outbound messages in the database to assist in troubleshooting communication between those applications
  • Because the field type is XML rather than a generic string type (i.e. VARCHAR), we're able to use XQuery to specifically query for messages matching some specific criteria. This would be much more difficult with simple string LIKE matching
• Caching/persisting a composed response message
  • Update the XML field with a trigger or application code, which would mimic a computed column
  • Instead of constantly regenerating an XML response to a calling application based you would incur overhead only when the row is upserted, rather than on every call
  • This works best when SELECTs are much more frequent than UPDATEs/INSERTs, which tends to be true for most applications
• Storing multiple values in a single field
  • One of the practices I've seen is the use of XML data type to store a collection of values in a single field
  • One benefit is that you needn't design an extra set of tables for a simple key/value store
  • A downside of this is that the data isn't fully normalized, making querying less obvious
  • However, as mentioned above, you can use XQuery on that XML field to select rows which match the identifying criteria, thereby partially nuetralizing the impact of not being fully normalized.

All that said, 99% of the time, I have absolutely no need for an XML field when designing a schema.

Answer 3

I have used XML type fields mostly for logging purposes related to ASMX web services or WCF services. You can save the request or the response messages.

Most of the times, you save the XML in the DB for reference purposes - not for your regular business activity (not to query it every 5 seconds from the code). If you find yourself doing so, determine the fields that you usually query or use most of the time and create separate columns for them. That way, when you are saving the XML to the DB, you can extract these fields and save them to their corresponding columns. Later on while retrieving them you wouldn't need to query the XML document you can just use the columns you created for this purpose.

Answer 4

The few times that I've seen the XML data type in SQL server it's basically been used as a field that has been queried just like any other in the database, which can have some very bad performance implications if you have a large amount of data. There's a reason it's called SQL server and not XML server. :-) The queries that go against the XML are simply not as fast as a doing regular select query.

I could see it being used to store XML that isn't queried that much though, say storing the full XML document in a XML data type, but having the searchable fields(keys) stored separately with the XML document. That way you can query your information faster and pull up the XML document when you get to the point where you want to see the full document. I've actually had to go back and do this with a number apps where people have tried to use the XML data type as a heavily queried field and the data has grown to the point where the query has gotten too slow.

Answer 5

These are the scenarios that come to mind immiediately at a whim when considering what conditions would need to exist to permit Xml fields in Sql Server:

• binary data encoded for interchange where you want acid like control of the resource
• document fragments that will be reconstituted in whole using dynamic information
• user submitted documents or fragments that you want to isolate and at least on an interim basis keep off the file system directly.

Answer 6

I've done a similar thing where I serialize object data to XML for storage. This removes the burden of having tables, columns, and relationships in place to hold data for complex objects. The process can be helped by using a schema that the result XML conforms to, and the database tables can be used for meta information about the objects in question. In my case, expanding database schema to accommodate the object layout would be a big task!

Answer 7

I use XML extensively in client-server apps for saving structured data in a single call to the database. As an example take an Invoice with Detail lines. It's simple to have the client serialise the business object into XML and pass to a stored proc in a single call. The proc decides whether an insert or update is required; it can get the parent invoice ID (an identity column) to assign to the detail records, all within a server-side transaction and without the client having to make several round trips. I don't need 20 or so parameters to specify the header record and I don't have to make a call for every invoice line. Also, it's often possible to make schema changes, or introduce logging/auditing, without having to touch the client.