XML - Pro SQL Server Internals - page 208

As you see, one row of data in the original table produced twenty-five rows in the primary XML index with
twelve columns each. The clustered index of the primary XML index consists of the primary key in the original
table ( pk1 column in the output) and internal node id ( id column in the output). The HID column, which stands for
hierarchy id, contains a reverse path to the node in the binary format.
It is also worth mentioning that the primary XML index requires a table to have the clustered primary key
defined. Neither a unique clustered index nor a non-clustered primary key will work.
Now let's create a schema collection and construct the table using typed XML. The code for accomplishing this is
shown in Listing 11-5.
Listing 11-5. Primary XML index on typed XML
create xml schema collection XmlDemoCollection as
N'<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Order">
<xs:complexType>
<xs:sequence>
<xs:element type="xs:int" name="CustomerId"/>
<xs:element type="xs:string" name="OrderNum"/>
<xs:element type="xs:dateTime" name="OrderDate"/>
<xs:element name="OrderLineItems">
<xs:complexType>
<xs:sequence>
<xs:element name="OrderLineItem" maxOccurs="unbounded" minOccurs="0">
<xs:complexType>
<xs:sequence>
<xs:element type="xs:short" name="ArticleId"/>
<xs:element type="xs:int" name="Quantity"/>
<xs:element type="xs:float" name="Price"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute type="xs:int" name="OrderId"/>
<xs:attribute type="xs:float" name="OrderTotal"/>
</xs:complexType>
</xs:element>
</xs:schema>';
create table dbo.XmlTypedDemo
(
ID int not null identity(1,1),
XMLData xml (document xmldemocollection) not null,
constraint PK_XmlTypedDemo
primary key clustered(ID)
);