I have two queries as shown below which do the same thing. Here xmlcolumn is a column with datatype XML. I use these queries to search for a string anywhere in the XML column.
I checked the execution plan of both these queries and find that the first query has a lower I/O cost and subtree cost than second one. I was expecting that the first one would have a higher cost as it uses cast and charindex, but that is not the case.
Why does it have a lower cost?
First query:
SELECT *
FROM mytable
WHERE ( Charindex('abc',CAST([xmlcolumn] AS VARCHAR(MAX)))>0 )
Second query:
SELECT *
FROM mytable t1
WHERE t1.[xmlcolumn].exist('//*/text()[contains(.,"abc")]')=1
Best Answer
Your queries are not equivalent. The first will give you rows where both node names and values match where your second query only check the node values.
SQL Fiddle
The extra cost for the second query comes from using a table valued function to shred the XML.
First query:
Second query: