How to query xml column in tsql
74,725
Solution 1
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
Solution 2
DECLARE @t XML = '
<Event>
<Indicator>
<Name>GDP</Name>
</Indicator>
<Announcement>
<Value>2.0</Value>
<Date>2012-01-01</Date>
</Announcement>
</Event>
<Event>
<Indicator>
<Name>Other</Name>
</Indicator>
<Announcement>
<Value>3.0</Value>
<Date>2012-01-01</Date>
</Announcement>
</Event>
'
SELECT node.value('.', 'NUMERIC(20, 2)')
FROM @t.nodes('/Event[Indicator/Name/node()=''GDP'']/Announcement/Value') t(node)
Author by
fivelements
Updated on November 03, 2020Comments
-
fivelements over 3 years
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 to find all the rows related to "GDP" Indicator;
- How to get all values for "GDP" Indicator;