When dealing with XML columns, you can improve search times by building indexes on the values extracted from the XML. The index persists the value for searches so there is no need to re-extract the value until you actually retrieve the record after finding it.
Tonight, I was helping a friend and it required a view from an inner join of two different tables and getting values from each table’s XML to be coalesce. So the logical solution is to create a VIEW and then index the xml values. I could create the columns for each value and proceed that way – but it consumes resources that are not absolutely required.
Wait! You cannot create indexes on views containing XML data type methods. If you try, you will get this error message:
Msg 1985, Level 16, State 1, Line 1
Cannot create index on view 'VerraTerraDatabase.dbo.CurrentData2'. It contains one or more XML data type methods.
i.e. If you try something like:
However the code below will work and create indexes on XML values through the use of functions.
What we do is create a function wrapper, for example this lean definition:
Result: I have a join on two tables containing XML columns with the coalesce of values from the xml in each table being indexed. For most of you this is not likely something you need often, but it can simplify a data model.