Database Reference
In-Depth Information
Figure 11-6.
Execution plan when the XML index is present
As you see, SQL Server retrieves the data from the primary XML index rather than using a table-valued function.
exists() Method
The
exist()
method returns 1 when XQuery/XPath returns non-empty results. Although you can use this method
when you need to check for the existence of an XML node, the typical use-case for such a method is to check for the
existence of the element or attribute with a specific value.
This method usually outperforms the approach that shreds the XML using the
value()
method and compares
the results afterwards. This happens because you are evaluating the XPath predicate in the XML Reader rather than
doing an evaluation after you shred the XML. You can also use the
sql:column()
and
sql:variable()
functions to
pass the values from the variable or table column to the XPath predicate.
Another important factor is that the
exist()
method can utilize a secondary
FOR VALUE
XML index while the
value()
method does not use it.
Now let's create that index and compare the performance of the two methods. The code for accomplishing this is
shown in Listing 11-11, and the execution plans are shown in Figure
11-7
.
Listing 11-11.
Comparing exist() and value() methods
create xml index XML_Value on dbo.ElementCentricUntyped(XMLData)
using xml index XML_Primary_ElementCentricUntyped for value;
select count(*)
from dbo.ElementCentricUntyped
where XmlData.exist('/Order/OrderNum/text()[.="10025"]') = 1;
select count(*)
from dbo.ElementCentricUntyped
where XmlData.value('(/Order/OrderNum/text())[1]','varchar(32)') = '10025'
