XML documents are, by design, at best like an entire relational database, stored in a single column. Worst-case, they don’t even come with a schema. Small wonder then, that XML queries take a good deal of CPU and I/O performance to complete. The solution to this problem is to index them, pretty much like you would index regular tables.
xml index
Typed XML data
Did you know that you can define an XML schema for every XML column or variable that you want? Defining XML schemas is not only good practice, because it validates the data as soon as you store it in the column or variable, but it also improves XML query performance.
Parsing XML data
You’ll be hard-pressed not to stumble over XML data in your daily work. Good thing then, SQL Server contains built-in XML parsing logic – there’s a native xml datatype, built-in XPath support and all the tools you need to store and transform data from or to XML data. However, this is an extensive topic, so this article will cover the basics of parsing XML data into a recordset, just to wet your apetite.
sqlsunday.com 