Allen White

XML in SQL Server - a good idea but proceed with caution

I've had a really hard time trying to understand the real value of having an XML data type in SQL Server. Last week I taught the Microsoft class 2779B Implementing a Microsoft SQL Server 2005 Database, and struggled through the section on XML because I haven't been able to find a good use for it.

Last Thursday evening Dave Wilson of Progressive Insurance shared with the Ohio North SQL Server User's Group how Progressive is getting ready to implement loosely structured data - XML - in their applications. The problem is that every state has different data requirements for insurance reporting for every type of vehicle being insured. Another big problem is the rules that establish the data requirements change frequently. Maintaining relational structures to support the variety of data structures required has become a monumental task.

The team from Progressive went to Redmond to test their solutions and learned a lot about XML indexes and size requirements. The company has over a terabyte of data they have to maintain. Without an index on the XML data the data's useless, so they built a primary index (which shreds the XML and creates an internal representation of the data that SQL Server can search) and then three secondary indexes, a Path index, a Property index and a Value index. Once these were created the size on disk for the XML data went from 1TB to just under 9TB! They did a number of tests and found they could live with just the Property index, and the size on disk after that was 2.7TB.

It's good to see that Microsoft has provided a solid foundation for companies like Progressive to use XML for loosely structured data. It's also important to note the cost of implementing a solution like that. I hear comments that the Relational Model is dead because of things like XML. As is true in so many aspects of life, one tool does not solve all problems.

Allen