7

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

Mark Amery
  • 143,130
  • 81
  • 406
  • 459
doshea
  • 73
  • 1
  • 3

1 Answers1

7

Try this:

SELECT *
FROM XmlTest
WHERE Data.exist('/data/item[type[@v=''3''] and value[@v=''DEBIT'']]') = 1
Matt Gibson
  • 37,886
  • 9
  • 99
  • 128
  • 1
    +1 DARN ! I just came up with this solution after a bit of research.... only to find you've been 6 minutes faster..... ARGH! :-) Good call, though! – marc_s Jun 24 '10 at 11:07
  • 1
    Perfect. Thanks Matt & Marc_s for your assistance. – doshea Jun 24 '10 at 12:04