Menu
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> 
628
Using the value() method against an xml type variable. In the following example, an XML instance is stored in a variable of xml type. The value() method retrieves the ProductID attribute value from the XML. The value is then assigned to an int variable.
Now as I said before, XML data stored in a column of data type XML can be processed either by using XML functions available in SQL Server or by using the sp_xml_preparedocument stored procedure along with the OPENXML function.
SQL Server lets you retrieve data as XML by supporting the FOR XML clause, which can be included as part of your query. You can use the FOR XML clause in the main (outer) query as well as in subqueries. The clause supports numerous options that let you define the format of the XML data.
You should use the query() Method if you want to get a part of your XML. If you want the value from a specific node you should use value() Method. Update: If you want to shred your XML to multiple rows you use nodes() Method.
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
143
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
