-- Relational indexes
ALTER TABLE OrdersXML_Attr ADD CONSTRAINT OrdersXML_Attr_XPK PRIMARY KEY (id)
GO
ALTER TABLE OrdersXML_Elem ADD CONSTRAINT OrdersXML_Elem_XPK PRIMARY KEY (id)
GO
CREATE UNIQUE INDEX Orders_XK1 on Orders (id)
GO
CREATE INDEX OrderDetails_XK1 on OrderDetails (id,code)
GO

-- XML indexes
CREATE PRIMARY XML INDEX OrdersXML_Attr_PRIMARY on OrdersXML_Attr (x)

GO

CREATE XML INDEX OrdersXML_Attr_VALUE ON OrdersXML_Attr(x)

USING XML INDEX OrdersXML_Attr_PRIMARY FOR VALUE

GO

CREATE PRIMARY XML INDEX OrdersXML_Elem_PRIMARY on OrdersXML_Elem (x)

GO

CREATE XML INDEX OrdersXML_Elem_VALUE ON OrdersXML_Elem(x)

USING XML INDEX OrdersXML_Elem_PRIMARY FOR VALUE

GO

Queries we used in our experiment:

(*) attribute-style query
select id, x.value('(/Orders/OrderDetails/@code)[1]','int') as o
  from OrdersXML_Attr
 where x is not null and
       x.exist('/Orders/OrderDetails [@code="0123"]')=1

(**) element-style query

select id, x.value('(/Orders/OrderDetails/code)[1]','int') as o

  from OrdersXML_Elem

 where x is not null and

       x.exist('/Orders/OrderDetails [code="0123"]')=1

(***) normal relational query

select distinct o.id, d.code, o.doc_number

  from Orders o, OrderDetails d

 where o.id = d.id and d.code = '0123'

In this table you can see results of our investigation. We provide 2 results in each cell, one for 'hot' execution (2nd or 3rd execution of the same query) and the second value for 'cold' execution (a very first executuon with void buffer cache, after execution of DBCC DROPCLEANBUFFERS)

As you can see, results in attribute-style are 10-15 times worse the for the traditional, relational storage of data. Not so bad, at least, much better, then FULL TABLE SCAN. In any case we recommend to perform tests on your modelk data before you went too far. Bad surprise is degradation of performance when we use element-type style of storage. Absolute values are too big and grows proportionally of the table size so you must thing properly about using this type of storage. We will try to examine this situation in our future research.

It might be also interesting to compare the disk storage required to store 2 relational tables and data, occupied by XML:

 (*) we convert xml field 'x' to text using convert(varchar(max), x) and then count the length of all rows: sum(len(convert(varchar(max), x)))

Conclusion

As usual, XML is nbiot a silver bullet. It is not a good idea to replace everything with XML. But you can use it rwhere eally fits, where data structured are not stable, are badly defined, changes every day, come from external data sources etc.