July 28, 2008 at 9:44 am
All,
I want to count all the records where value of Code is "Minor". Does anyone have any suggestions on how to do this?
This is what I have so far. It works but I get '1 1' in the resultset in stead of 2
select
xmlData.query(for $s in (test/violation)
where data($s/Code) = "Minor"
return count($s/Code)')
from table
Below is the XML:
July 28, 2008 at 11:29 am
You'll need to wrap the count in a sum.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 28, 2008 at 12:15 pm
The following statement doesn't work either. It still returns '1 1'
select
xmlData.query(for $s in (test/violation)
where data($s/Code) = "Minor"
return sum(count($s/Code))')
from table
July 28, 2008 at 4:06 pm
We cannot see your XML. Please attach it as a separate file.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 29, 2008 at 9:08 am
I have attached the xml.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply