SQL Server 2008R2 XML Column Index strategy for large tables? I am working with SQL Server 2008 R2 and some XML columns in a very large table. I understand that I cannot use Selective XML Indexes due to our SQL Server version being &lt; 2012. I'm brand spanking new to the idea of indexing XML columns.

The Goals

I have two separate scenarios in which I need to query XML:


In one case, I need to query if a nested value exists across a table with 3 million rows

E.g. columnName.exist('Parent[1]/Child[1]/DifferentChild[1]') = 1'

In another case, I need to extract two values from XML into two columns so that I can match on it, in a table with 4.7 million rows.

E.g. SELECT columnName.value('Parent[1]/FilePath[1]', 'nvarchar(max)') as FilePath, columnName.value('Parent[1]/FileName[1]', 'nvarchar(255)') as FileName



Question


Given the cost of the indexes here and the fixed type of querying, what are indexes I can apply for maximum impact, and are there any pitfalls I should be aware of?
You should have a look at Property Promotion in XML Best Practices for Microsoft SQL Server 2005.

Create a user defined scalar valued function that extracts the value you need and use the function as a computed column in your table. You can persist the column of you like and you can create a regular index on the column. You don't have to persist the column in order to create the index.