I have a table, T1, with a XML column, EventXML, on SQL Server 2008. I want to query all the rows where certain node contains a particular value. Better, I'd like to retrieve the value in a different node. The table T1:
T1:
EventID, int
EventTime, datetime
EventXML, XML
Here is an example XML hierarchy:
<Event>
<Indicator>
<Name>GDP</Name>
</Indicator>
<Announcement>
<Value>2.0</Value>
<Date>2012-01-01</Date>
</Announcement>
</Event>
How about this?
SELECT
EventID, EventTime,
AnnouncementValue = t1.EventXML.value('(/Event/Announcement/Value)[1]', 'decimal(10,2)'),
AnnouncementDate = t1.EventXML.value('(/Event/Announcement/Date)[1]', 'date')
FROM
dbo.T1
WHERE
t1.EventXML.exist('/Event/Indicator/Name[text() = "GDP"]') = 1
It will find all rows where the /Event/Indicator/Name
equals GDP
and then it will display the <Announcement>/<Value>
and <Announcement>/<Date>
for those rows.
See SQLFiddle demo