Pros and Cons of using XML data type in SQL Server 2005

Recently I developed a web-based tool using ASP.NET and C#, that enables users to create custom forms in a matter of minutes.Every form can have any number of controls like Textboxes, Radiobuttons,Checkboxes,Labels etc.Since the form size can vary from say 5 fields to 20 fields to 50 fields,there was no way I could create a flat table structure to store this data. So I decided to use the XML data type in SQL Server 2005 to store the form fields configuration.

The rendering part of the tool parses this xml configuration(using LINQ to XML) and renders the form.Let us assume that this form is going to be used for survey purposes. The survey users can fill in the data and complete the survey. All this works just fine and the survey is successfully completed.

Then comes the reporting(SSRS). The management wants to see the results grouped and sorted in a multitude of ways. SQL Server does not have too much of an API for querying XML columns. So I had to use whatever was available(the query,value,exist,modify and nodes methods) along with some SQL cursors to come up with the desired results in the desired format.

The problem with this approach was that this parsing(which was happening at runtime) took time and the time taken was directly proportional to the number of survey instances, so as the data grew, the reporting became slower and slower. So while XML was very convenient way to store variable length data, it was not very easy to work with, in terms of reporting or analysis purposes.

I managed to solve the issue with reporting by creating a flat table structure for storing the survey data(since the configuration is constant once the form is created).I then created a SQL agent that runs as a scheduled task during the non-peak hours and performs the time consuming task of parsing instance xml data and populating the flat table. I then pointed the reports to the flat table. This speeded up the reporting process.

So while XML data type is suitable for storing variable length data, the developers need to note that it is not very easy to work with the XML, with the limited API in SQL Server. One way to solve the problem would be to get the data out of SQL and use the powerful .NET API to get the desired result. The other way would be to create SQL agent(as described above) to pre-populate data into flat tables and then work with those tables. But all said and done, the XML data type is a very powerful feature and provides tremendous flexibility. I would definetely recommend it.