Quiz: SQL Server 2005 XML 'exist' method

  • Article

Among the features introduced in SQL Server 2005 is the new XML Datatype. You can use this guy to store both untyped and typed XML information and maintain the infoset fidelity of the document. We support a subset of the XQuery language which can be used to retrieve values from XML instances and reshape documents, amongst other things. One particularly useful, although sometimes confusing, piece of functionality is the "exist" method. The semantics are that if the XQuery statement evaluates to a node, then it returns true, if it evaluates to the empty set, then it returns false. It is invoked like this:

 declare @x xml
set @x = '<Person><Name>John</Name><Age>24</Age></Person>'
select @x.exist('/Person/Name[.="John"]')

The result of this query is the boolean (bit in T-SQL) value '1' indicating that there does exist a "/Person/Name" node with value "John". 

Ok, so now that you know that, what's wrong with this query? (Update: The intention is to get the customer_info for any customer named "John".)

 create table customers (
 customer_id int primary key identity(1,1),
 customer_info xml
 )
go
select customer_info
from customers
where customer_info.exist('/Customer/Name = "John"') = 1

I'll let you think about a bit and post back next week with what is wrong, why it is wrong, and how to correct it.