XML - Pro SQL Server Internals - page 213

■ Note the actual size of the primary XML index depends on the number of nodes and their data types in the

XML data.

Secondary XML indexes are non-clustered indexes in a table that are represented by the primary XML index.
Look at Table 11-2 , which demonstrates a simplified version of some of the data from the primary XML index table
from Figure 11-3 .

Table 11-2. Primary XML index simplified

PK ID NodeId Type Value HID

1 1 1 (Order) Null Null

1 1.1 2 (OrderId) xs:int 42 #@OrderId#Order

1 1.5 3 (OrderLineItems) SectionT Null #OrderLineItems#Order

1 1.5.1 4 (OrderLineItem) SectionT Null #OrderLineItem #OrderLineItems#Order

1 1.5.1.1 5 (ArticleId) xs:int 250 #ArticleId #OrderLineItem #OrderLineItems#Order

The VALUE secondary XML index is a non-clustered index with two columns: Value and HID . As you can guess,
the best use-case for such indexes is when you want to locate the rows based on the values and optional paths to the
nodes. In our example, the VALUE secondary XML index would be beneficial if you want to find all of the orders that
have the line item with specific ArticleID .
The PATH secondary XML index has two columns: HID and Value . Like the VALUE index, the PATH index can be
used to find all of the rows with a particular value in a particular path, although there are a couple differences between
those indexes. The VALUE index can be used to find the XML element or attribute with a specific value anywhere
within the XML without referencing the path. The PATH index, on the other hand, is not a good choice for such a
use-case. The PATH index, however, is useful when you are checking the existence of an element based on a particular
path. For instance, the PATH index is advantageous if you have an optional nullable node called Comments , and you
want to select all of the orders where that node is present. Moreover, the PATH index is useful when you are using the
// shortcut in the path. For example, Order//ArticleId looks for an ArticleId element anywhere within the Order
node. HID stores the inverted path and, as a result, SQL Server can perform a prefix lookup on the index when dealing
with such queries.
The PROPERTY secondary XML index has three columns: PK , HID , and Value . This index is useful when you
already know the row to which the XML belongs, and you want to get the value and potential node information for
the specific path.
SQL Server 2012 supports selective XML indexes that allow you to index a subset of the XML nodes.
These indexes help you to preserve the storage space when the majority of the queries deal with a subset
of the XML data. For more information about selective XML indexes, check out this link:
http://msdn.microsoft.com/en-us/library/jj670108.aspx .

Working with XML Data
The XQuery implementation in SQL Server utilizes a relation engine. Although XQuery uses its own parser and
performs its own algebrarization during the query compilation stage, the results are combined and optimized
together with the DML portion of the query and embedded into a single execution plan.