Database Reference
In-Depth Information
<OrderLineItem>
<ArticleId>404</ArticleId>
<Quantity>1</Quantity>
<Price>19.99</Price>
</OrderLineItem>
</OrderLineItems>
</Order>
In some cases, when the data is semi-structured, you can shred the structured part into non-XML columns,
retaining the semi-structured part as XML. Listing 11-2 shows an example of this. In this case, you can consider
shredding and keeping location-related information in the non-XML columns and keeping
DeviceData
information
as XML.
Listing 11-2.
Semistructured XML
<Locations>
<Location DeviceId="321432345" Timestamp="2013-07-10T09:01:03">
<Latitude>47.609102</Latitude>
<Longitude>-122.321503</Longitude>
<DeviceData>
<Ignition>1</Ignition>
<Sensor1>0</Sensor1>
<Sensor2>1</Sensor2>
</DeviceData>
</Location>
<Location DeviceId="1563287" Timestamp="2013-07-10T09:02:00">
<Latitude>47.610611</Latitude>
<Longitude>-122.201202</Longitude>
<DeviceData>
<Speed>56</Speed>
<Temperature>29</Temperature>
</DeviceData>
</Location>
</Locations>
Using sparse columns is another option. You can create a wide table with a large number of sparse columns that
represent all possible attributes from the XML Data without introducing the storage overhead associated with the
storage of NULL values.
You can shred the XML in the code at the time that you insert or update the data. Alternatively, you can create a
set of scalar user-defined functions that extract the data from XML and store it in the persisted, calculated columns.
Both approaches have their pros and cons. With the first approach, you need to shred the XML data and update the
columns every time when the XML data is updated, potentially in different places in the code. The second approach,
on the other hand, can lead to some performance issues. User-defined functions, which shred the data into calculated
columns, would prevent parallel execution plans for the queries that are referencing the table, even when
calculated columns are not used.
Although XML adds flexibility to our data model, it affects the performance of the system. You must always keep
this in mind when designing solutions.
