SQL Column exist () query SQL Server

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 check for elements of an element with type v = "3" and value v = "DEBIT".

I use 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 returns me strings with XMLid 1 and 3.

Can someone state what change I need to make for the WHERE clause to only return a record that has an element where the value of type node v is 3 and the value of node v is "DEBIT"? i.e. only write using xmlid 1

thanks

Answer 1

Try the following:

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