Last week I threw out a quick quiz about the XML datatypes 'exist' method. The reason it was at the top of my mind was that it was a mistake that I had made only a couple of days before. Not only that, but even a couple of my peers didn't recognize the mistake when I showed it to them. We all knew the rule, however we all managed to look at the same piece of code (albeit a bit more complicated than what I presented) and never notice the problem.
Ok, to recap we had the following bit of T-SQL which was trying to get the customer_info for all customers named "John":
where customer_info.exist('/Customer/Name = "John"') = 1
Its probably no big shocker that the problem is with the XQuery exist method invocation. The rule is that if exist returns any results then it returns 1, if the result is the empty set (represented by '()' in XQuery). Even if that result is the atomic value false(). In this case, It will always return either true() or false() due to the way that that '=' operator works in XQuery. In other words, this XQuery statement will return true() if any /Customer/Name has value "John", and false() otherwise. Now, since it will return 1 for any instance, we have a problem because our filter is essentially "where 1=1".
How do I go about fixing it? There are actually a couple of ways, I can write something like this: (/Customer/Name = "John")[.=true()]. However, this isn't exactly straightforward. Instead, I can add a predicate to the "Name" node test which checks that its contents are equal to "John". The complete rewritten XQuery is the following:
where customer_info.exist('/Customer/Name[. = "John"]') = 1
There is actually one other trick that I can play if I want to compare "John" to the text node contained directly in the element "Name". To do that I can write this:
where customer_info.exist('/Customer/Name/text()[. = "John"]') = 1
And this will actually perform much better in many cases. The reason why though is the topic for another blog altogether.