Delen via

Quiz: SQL Server 2005 XML 'exist' method

  • Artikel

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.

Comments

  • Anonymous
    June 10, 2005
    Wouldn't this get back all customer_info (the where condition is not specific to a particular customer_info, but to the existence of any customer_info)?
  • Anonymous
    June 10, 2005
    I think the SELECT statement will always return all the rows in the customers table. The expression /Customer/Name = "John" will return true or false and hence a nonempty result, in which cases the exist method would return 1. So far each row, exist would return 1; and so 1 = 1 matches for all rows and the SELECT statement will return all the rows in the table. The correct syntax would be the way you have in the first example with the @x variable and condition in square brackets.
  • Anonymous
    June 13, 2005
    Last week I threw out a quick quiz about the XML datatypes 'exist' method.&amp;nbsp; The reason it was at...
  • Anonymous
    June 13, 2005
    Although I am embarassed by my quick post, I like this problem. good one.