Database Reference
In-Depth Information
Figure 11-7.
Comparing the exist() and value() methods
In the case when there is no
FOR VALUE
secondary XML index present, however, the
value()
method may
be more efficient than the
exist()
method. There is one more caveat. XQuery compares string data as unicode
case-sensitive strings, and it does not take database collation into consideration. Consequently, you can have
different results when you perform a comparison within XQuery
value()
method. The code shown in Listing 11-12
demonstrates an example of such behavior.
Listing 11-12.
String comparison within XQuery
declare
@X xml = '<Order OrderNum="Order1"><OrderId>1</OrderId></Order>'
,@V varchar(32) = 'ORDER1'
select 'exist(): found' as [Result]
where @X.exist('/Order/@OrderNum[.=sql:variable("@V")]') = 1
select 'value(): found' as [Result]
where @X.value('/Order[1]/@OrderNum','varchar(16)') = @V
As you see in Figure
11-8
, the
exist()
method compares the
OrderNum
attribute and the
@V
variable with case
sensitivity, and it produces a different comparison result in T-SQL when case-insensitive collation is used.
