CODEWITHSUNDEEP
sql-serverxmlt-sqlsqlxml
Chris Whisenhunt
Chris Whisenhunt

Reputation: 351

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

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?

Upvotes: 0

Views: 887

Answers (2)

roman
roman

Reputation: 117540

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

Upvotes: 1

podiluska
podiluska

Reputation: 51504

;with cte as 
(
SELECT
    ID, t1.XMLFIELD, row_number() over (partition by ID order by ID) rn
FROM 
    t1
CROSS APPLY 
    XmlField.nodes('/ITEMS/ITEM') as XTbl(XItem)
)
    select XMLFIELD from cte
    where ID IN (
    select ID from cte 
    where XMLFIELD.exist('/ITEMS/ITEM/FIELD[.="Payment method"]') = 1
    and XMLFIELD.exist('/ITEMS/ITEM/NEWVALUE[.="25"]') = 1)
    and rn=1

Or simpler...

select
    *
from 
    t1
where
    XMLFIELD.value('count(/ITEMS/ITEM/FIELD[.="Payment method"])','int') > 0
    and 
    XMLFIELD.value('count(/ITEMS/ITEM/NEWVALUE[.="25"])','int') > 0

Upvotes: 1

Related Questions