How to query xml column in tsql

by

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

More Related Contents:

  1. Convert Xml to Table SQL Server
  2. SQL Server SELECT to JSON function
  3. SQL: How can I get the value of an attribute in XML datatype?
  4. Concatenate grouped rows
  5. Using XPath, How do I select a node based on its text content and value of an attribute?
  6. Concatenate multiple node values in xpath
  7. How can I use xpath querying using R’s XML library?
  8. How can I retain CDATA tags when storing query results in an Xml variable?
  9. I dont know sql syntax for my table [closed]
  10. How to reference a local XML Schema file correctly?
  11. Common Table Expression, why semicolon?
  12. T-SQL split string based on delimiter
  13. How to get the last record per group in SQL
  14. XSLT: A simple way to merge xml files
  15. Libraries and tutorials for XML in Delphi
  16. In XSLT how do I increment a global variable from a different scope?
  17. The ‘minOccurs’ attribute cannot be present
  18. SQL Recursive CTE: Finding objects linked by property
  19. Get nodes where child node contains an attribute
  20. T-SQL substring – separating first and last name
  21. Spec. for JUnit XML Output [closed]
  22. Powershell – Creating new entries into an XML File
  23. XPath to return only elements containing the text, and not its parents
  24. Looking for a clear description of Excel’s .xlsx XML format [closed]
  25. How to combine results of two queries into a single dataset
  26. “Content is not allowed in prolog” error yet nothing before XML declaration
  27. Scala – modifying nested elements in xml
  28. Dealing with malformed XML [duplicate]
  29. Select rows where column value has changed
  30. How do you read XML column in SQL Server 2008?

Leave a Comment