sql server - Selecting from XML field where XML field = X and another XML field = Y -

so build off of question, selecting xml field xml field = x, , using same sql fiddle, http://sqlfiddle.com/#!3/7c0a0/5.

i can't seem figure out how grab record has both item has field value of 'payment method' , has item has newvalue of 25.

i tried following , didn't return results. assuming because it's looking in clause in 1 of nodes doesn't exist.

select     id      t1 cross apply      xmlfield.nodes('/items/item') xtbl(xitem)     xitem.exist('field[.="payment method"]') = 1     , xitem.exist('newvalue[.="25"]') = 1 

what missing?

if want single items condition holds can do:

select     t1.id, t.c.query('.') t1     cross apply xmlfield.nodes('/items/item[field[.="payment method"] , newvalue[.="debit"]]') t(c) 

or

select     t1.id, t.c.query('.') t1     cross apply xmlfield.nodes('/items/item') t(c)     t.c.value('field[1]', 'nvarchar(max)') = 'payment method' ,     t.c.value('newvalue[1]', 'nvarchar(max)') = 'debit' 

sql fiddle demo

after comment, think, may need this:

select     t1.* t1       xmlfield.exist     ('/items[           item[field[.="payment method"] , newvalue[.="debit"]] ,           item[datatype[.="4"] , newvalue[.="25"]]        ]'     ) = 1 

sql fiddle demo