XML - Pro SQL Server Internals - page 204

Chapter 11
XML
We are living in a world full of information. Businesses are constantly collecting large amounts of data from multiple
sources, processing it and exchanging it with other systems. XML has become the de-facto standard for information
exchange—it works across different platforms and is supported in every development platform that exists today.
Moreover, not all data easily fits into a structured relational data model. For example, we can think about a system
that collects metrics from different types of sensors. Some sensors might provide information about temperature,
while others could supply humidity data. Although there are several ways to store such data in a database, XML is
definitely an option worth considering.
In this chapter, we will talk about the XML data type, system design considerations, and a few methods that can
help to improve system performance while working with XML data in SQL Server.
To Use or Not to Use XML? That Is the Question!
One of the key questions you will need to answer when dealing with XML data in a database is what use-cases you
need to support. Although XML gives you the flexibility to deal with semi-structured data, it comes at a price. XQuery
is CPU-intensive, and it does not provide performance on par with queries against relational data. You can overcome
some of these limitations by creating XML indexes, which internally shred XML data to the relational format, but
those indexes require a lot of storage space—often several times more than the XML data itself.
In the case where the only requirement is keeping the XML data without any further processing, the best
approach is to store it as regular BLOB in the varbinary(max) column. This allows reconstructing the original
document without any encoding-related issues introduced by varchar / nvarchar data types. The XML data type is
not a good choice either as it does not preserve the original document. Even when it is acceptable, there is overhead
associated with parsing the XML data that you would prefer to avoid.
When you need to work with XML data in SQL Server, you have a few choices. If the data fits into a structured
relational model, you will get the best performance by shredding and storing it in a relational table format. For
example, you can shred and store XML, as shown in Listing 11-1, into two tables— Orders and OrderLineItems .
Listing 11-1. XML that fits into a relation model
<Order OrderId="42" OrderTotal="49.96">
<CustomerId>123</CustomerId>
<OrderNum>10025</OrderNum>
<OrderDate>2013-07-15T10:05:20</OrderDate>
<OrderLineItems>
<OrderLineItem>
<ArticleId>250</ArticleId>
<Quantity>3</Quantity>
<Price>9.99</Price>
</OrderLineItem>