SQL Server XML Column exist() query

Question

Assuming I have a SQL Server 2005 table with an xml column containing the following values:

CREATE TABLE XmlTest (
   XMLid int,
   Data xml)

INSERT XMLTest 
VALUES ( 1 , '<data><item><type v="1" /><value v="12.56" /></item><item><type     v="3" /><value v="DEBIT" /></item></data>' )

INSERT XMLTest 
VALUES ( 2 , '<data><item><type v="1" /><value v="99.22" /></item><item><type v="3" /><value v="CREDIT" /></item></data>' )

INSERT XMLTest 
VALUES ( 3 , '<data><item><type v="3" /><value v="12.56" /></item><item><type v="1" /><value v="DEBIT" /></item></data>' )

I want to test for the existance of item elements with a type v="3" AND a value v="DEBIT".

I am using the exist() function as follows:

SELECT *
FROM XmlTest
WHERE Data.exist('/data/item/type[@v=''3'']') = 1
AND Data.exist('/data/item/value[@v=''DEBIT'']') = 1

However this brings me back rows with XMLid 1 and 3.

Can anyone outline what change I need to make to my WHERE clause to return only record which have an item where the type node v value is 3 and the value node v value is "DEBIT"? i.e. only record with XMLid 1

Thanks

Answer 1

Try this:

SELECT *
FROM XmlTest
WHERE Data.exist('/data/item[type[@v=''3''] and value[@v=''DEBIT'']]') = 1