Selecting from XML field where XML field = X and another XML field = Y

Question

So to build off of this question, Selecting from XML field where XML field = X, and using the same SQL Fiddle, http://sqlfiddle.com/#!3/7c0a0/5.

I can't seem to figure out how to grab the record that has both an item that has a FIELD with the value of 'Payment method' and has an item that has the NEWVALUE of 25.

I tried the following and it didn't return any results. I am assuming because it's looking for everything in the where clause in one of the nodes which doesn't exist.

SELECT
    ID
FROM 
    T1
CROSS APPLY 
    XmlField.nodes('/ITEMS/ITEM') as XTbl(XItem)
WHERE
    XItem.exist('FIELD[.="Payment method"]') = 1
    and XItem.exist('NEWVALUE[.="25"]') = 1

What am I missing?

Answer 1

If you want only single items where your condition holds then you can do:

select
    T1.ID, T.C.query('.')
from T1 
   cross apply XMLFIELD.nodes('/ITEMS/ITEM[FIELD[.="Payment method"] and NEWVALUE[.="Debit"]]') as T(C)

or

select
    T1.ID, T.C.query('.')
from T1 
   cross apply XMLFIELD.nodes('/ITEMS/ITEM') as T(C)
where
    T.C.value('FIELD[1]', 'nvarchar(max)') = 'Payment method' and
    T.C.value('NEWVALUE[1]', 'nvarchar(max)') = 'Debit'

sql fiddle demo

After you comment, I think, may be you need something like this:

select
    T1.*
from T1 
where 
    XMLFIELD.exist
    ('/ITEMS[
          ITEM[FIELD[.="Payment method"] and NEWVALUE[.="Debit"]] and
          ITEM[DATATYPE[.="4"] and NEWVALUE[.="25"]]
       ]'
    ) = 1

sql fiddle demo