0

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?

Community
  • 1
  • 1
Chris Whisenhunt
  • 351
  • 2
  • 20

2 Answers2

1
;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
podiluska
  • 50,950
  • 7
  • 98
  • 104
  • So after looking at this more and more I don't think it's what I need. I was asking the wrong question. How would I look for a record that has a single that satisfies a specific condition and then another that satisfies another specific condition? Make sense? – Chris Whisenhunt Aug 15 '13 at 16:52
  • Okay so I want to select a record that has a equal to 'Payment method' and a equal to 'Debit' I need the condition to look inside a single . – Chris Whisenhunt Aug 15 '13 at 17:13
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

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
  • Perfect, so now with what you have what about adding in looking for another in the record's xml for two other conditions. For instance: what you have above and then also checking for an that has = 25 and = 4? – Chris Whisenhunt Aug 15 '13 at 17:59
  • Would adding cross apply in there twice and searching for the other conditions in the second cross work? It seems like it would, however I'm not sure about the performance with it. – Chris Whisenhunt Aug 15 '13 at 18:01
  • wait a sec, I have to realize what you want to do :) – Roman Pekar Aug 15 '13 at 18:03
  • lol, sorry. So this works for what I want, but I can't imagine that there isn't a better and more efficient way to do this then using cross apply twice. – Chris Whisenhunt Aug 15 '13 at 18:07
  • select T1.ID, T.C.query('.') from T1 cross apply XMLFIELD.nodes('/ITEMS/ITEM') as T(C) cross apply XMLFIELD.nodes('/ITEMS/ITEM') as S(C) where T.C.value('FIELD[1]', 'nvarchar(max)') = 'Payment method' and T.C.value('NEWVALUE[1]', 'nvarchar(max)') = 'Debit' and S.C.value('FIELD[1]', 'nvarchar(max)') = 'Amount' and S.C.value('NEWVALUE[1]', 'nvarchar(max)') = '25' – Chris Whisenhunt Aug 15 '13 at 18:08
  • 1
    have you checked my updated answer? it filter what you want, but I don't know what you want to select – Roman Pekar Aug 15 '13 at 18:10