Database Reference
In-Depth Information
</OrderLineItem>
<OrderLineItem>
<ArticleId>404</ArticleId>
<Quantity>1</Quantity>
<Price>19.99</Price>
</OrderLineItem>
</OrderLineItems>
</Order>'
from Ids;
insert into dbo.ElementCentricTyped(XMLData)
select XMLData from dbo.ElementCentricUntyped;
with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 CROSS JOIN N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 CROSS JOIN N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 CROSS JOIN N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 CROSS JOIN N4 as T2) -- 65,536 rows
,IDs(ID) as (select row_number() over (order by (select NULL)) from N5)
insert into dbo.AttributeCentricUntyped(XMLData)
select
N'<Order OrderId="42" OrderTotal="49.96" CustomerId="123"
OrderNum="10025" OrderDate="2013-07-15T10:05:20">
<OrderLineItem ArticleId="250" Quantity="3" Price="9.99"/>
<OrderLineItem ArticleId="404" Quantity="1" Price="19.99"/>
</Order>'
from Ids;
insert into dbo.AttributeCentricTyped(XMLData)
select XMLData from dbo.AttributeCentricUntyped;
When we compare the storage space used by all four tables, you see the results shown in Table
11-1
.
Table 11-1.
Typed and untyped XML storage requirements
Clustered Index
Size (KB)
Primary XML
Index Size(KB)
Total Size
(KB)
Untyped Element-Centric XML
28,906
90,956
119,862
Typed Element-Centric XML
45,760
52,595
99,355
Untyped Attribute-Centric XML
26,021
57,390
83,411
Typed Attribute-Centric XML
36,338
54,105
90,443
As you see, typed XML uses more space in the clustered index of the table due to the extra information stored
in the XML data type column. At the same time, adding type information to element-centric XML can significantly
reduce the size of the primary XML index. Unfortunately, even in a best case scenario, XML indexes require a large
amount of storage space that exceeds the storage space required by XML data type itself.
