How to query for Xml values and attributes from table in SQL Server?

by

Actually you’re close to your goal, you just need to use nodes() method to split your rows and then get values:

select
    s.SqmId,
    m.c.value('@id', 'varchar(max)') as id,
    m.c.value('@type', 'varchar(max)') as type,
    m.c.value('@unit', 'varchar(max)') as unit,
    m.c.value('@sum', 'varchar(max)') as [sum],
    m.c.value('@count', 'varchar(max)') as [count],
    m.c.value('@minValue', 'varchar(max)') as minValue,
    m.c.value('@maxValue', 'varchar(max)') as maxValue,
    m.c.value('.', 'nvarchar(max)') as Value,
    m.c.value('(text())[1]', 'nvarchar(max)') as Value2
from sqm as s
    outer apply s.data.nodes('Sqm/Metrics/Metric') as m(c)

sql fiddle demo

More Related Contents:

  1. How to query values from xml nodes?
  2. cross apply xml query performs exponentially worse as xml document grows
  3. Parse XML in SQL Server
  4. Milliseconds wrong when converting from XML to SQL Server datetime
  5. How do I fix the error ‘Named Pipes Provider, error 40 – Could not open a connection to’ SQL Server’?
  6. How to get Time from DateTime format in SQL?
  7. Select values from XML field in SQL Server 2008
  8. How much size “Null” value takes in SQL Server
  9. SQL Server Pivot Table with multiple column aggregates
  10. Why is it considered bad practice to use cursors in SQL Server?
  11. CAST and IsNumeric
  12. SQL Update with row_number()
  13. Is it possible to force row level locking in SQL Server?
  14. SQL Query to find the last day of the month
  15. SQL Server – Dynamic PIVOT Table – SQL Injection
  16. SQL Server, convert a named instance to default instance?
  17. Is a single SQL Server statement atomic and consistent?
  18. How to add xml encoding to xml Output in SQL Server
  19. SQL Server GUID sort algorithm. Why?
  20. WHERE clause on SQL Server “Text” data type
  21. SQL Server error on update command – “A severe error occurred on the current command”
  22. Unable to begin a distributed transaction
  23. What happens to the primary key Id when it goes over the limit?
  24. What is the point of “Initial Catalog” in a SQL Server connection string?
  25. SQL Server – defining an XML type column with UTF-8 encoding
  26. Compute MD5 hash of a UTF8 string
  27. Why is casting from float to varchar being rounded in SQL Server?
  28. What are row, page and table locks? And when they are acquired?
  29. The Best Way to shred XML data into SQL Server database columns
  30. SQL Server 2008 Spatial: find a point in polygon

Leave a Comment