How to query for Xml values and attributes from table in SQL Server?
Sql ServerXmlSql Server-2005Sql Server-2008-R2SqlxmlSql Server Problem Overview
I have a table that contains a Xml
column:
SELECT *
FROM Sqm
A sample of the xml
data of a row would be:
<Sqm version="1.2">
<Metrics>
<Metric id="TransactionCleanupThread.RecordUsedTransactionShift" type="timer" unit="µs" count="1" sum="21490" average="21490" minValue="73701" maxValue="73701" >73701</Metric>
<Metric id="TransactionCleanupThread.RefundOldTrans" type="timer" unit="µs" count="1" sum="184487" average="184487" minValue="632704" maxValue="632704" >632704</Metric>
<Metric id="Database.CreateConnection_SaveContextUserGUID" type="timer" unit="µs" count="2" sum="7562" average="3781" minValue="12928" maxValue="13006" standardDeviation="16" >12967</Metric>
<Metric id="Global.CurrentUser" type="timer" unit="µs" count="6" sum="4022464" average="670411" minValue="15" maxValue="13794345" standardDeviation="1642047">2299194</Metric>
<Metric id="Global.CurrentUser_FetchIdentityFromDatabase" type="timer" unit="µs" count="1" sum="4010057" average="4010057" minValue="13752614" maxValue="13752614" >13752614</Metric>
</Metrics>
</Sqm>
In the case of this data, I would want:
SqmId id type unit count sum minValue maxValue standardDeviation Value
===== =================================================== ===== ==== ===== ====== ======== ======== ================= ======
1 TransactionCleanupThread.RecordUsedTransactionShift timer µs 1 21490 73701 73701 NULL 73701
1 TransactionCleanupThread.RefundOldTrans timer µs 1 184487 632704 632704 NULL 632704
1 Database.CreateConnection_SaveContextUserGUID timer µs 2 7562 12928 13006 16 12967
1 Global.CurrentUser timer µs 6 4022464 15 13794345 1642047 2299194
1 Global.CurrentUser_FetchIdentityFromDatabase timer µs 1 4010057 13752614 13752614 NULL 13752614
2 ...
In the end I'll actually be performing SUM()
, MIN()
, MAX()
aggregation. But for now I'm just trying to query an xml column.
In pseudo-code, I would try something like:
SELECT
SqmId,
Data.query('/Sqm/Metrics/Metric/@id') AS id,
Data.query('/Sqm/Metrics/Metric/@type') AS type,
Data.query('/Sqm/Metrics/Metric/@unit') AS unit,
Data.query('/Sqm/Metrics/Metric/@sum') AS sum,
Data.query('/Sqm/Metrics/Metric/@count') AS count,
Data.query('/Sqm/Metrics/Metric/@minValue') AS minValue,
Data.query('/Sqm/Metrics/Metric/@maxValue') AS maxValue,
Data.query('/Sqm/Metrics/Metric/@standardDeviation') AS standardDeviation,
Data.query('/Sqm/Metrics/Metric') AS value
FROM Sqm
But that SQL query doesn't work:
> Msg 2396, Level 16, State 1, Line 2
> XQuery [Sqm.data.query()]: Attribute may not appear outside of an element
I've hunted, and it's amazing how poorly documented, or exampled, Xml querying is. Most resources rather than querying a table, query a variable; which I'm not doing. Most resources only use xml querying for filtering and selection, rather than reading values. Most resources read hard-coded child nodes (by index), rather than actual values.
Related resources that I read
- https://stackoverflow.com/questions/966441/xml-query-in-sql-server-2008
- https://stackoverflow.com/questions/12913724/sql-server-query-xml-attribute-for-an-element-value
- https://stackoverflow.com/questions/14343263/sql-querying-xml-attributes
- SQL Server 2005 XQuery and XML-DML - Part 1
- BOL: XML Support in Microsoft SQL Server 2005
- Querying XML in SQL Server
- Basic SQL Server XML Querying
- BOL: query() Method (xml Data Type)
- XML Workshop V - Reading Values from XML Columns
- SQL SERVER – Introduction to Discovering XML Data Type Methods – A Primer
Update: .value rather than .query
I tried randomly using .value
, in place of .query
:
SELECT
Sqm.SqmId,
Data.value('/Sqm/Metrics/Metric/@id', 'varchar(max)') AS id,
Data.value('/Sqm/Metrics/Metric/@type', 'varchar(max)') AS type,
Data.value('/Sqm/Metrics/Metric/@unit', 'varchar(max)') AS unit,
Data.value('/Sqm/Metrics/Metric/@sum', 'varchar(max)') AS sum,
Data.value('/Sqm/Metrics/Metric/@count', 'varchar(max)') AS count,
Data.value('/Sqm/Metrics/Metric/@minValue', 'varchar(max)') AS minValue,
Data.value('/Sqm/Metrics/Metric/@maxValue', 'varchar(max)') AS maxValue,
Data.value('/Sqm/Metrics/Metric/@standardDeviation', 'varchar(max)') AS standardDeviation,
Data.value('/Sqm/Metrics/Metric', 'varchar(max)') AS value
FROM Sqm
But that also doesn't work:
> Msg 2389, Level 16, State 1, Line 3 XQuery [Sqm.data.value()]:
> 'value()' requires a singleton (or empty sequence), found operand of
> type 'xdt:untypedAtomic *'
Sql Server Solutions
Solution 1 - Sql Server
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)
Solution 2 - Sql Server
I've been trying to do something very similar but not using the nodes. However, my xml structure is a little different.
You have it like this:
<Metrics>
<Metric id="TransactionCleanupThread.RefundOldTrans" type="timer" ...>
If it were like this instead:
<Metrics>
<Metric>
<id>TransactionCleanupThread.RefundOldTrans</id>
<type>timer</type>
.
.
.
Then you could simply use this SQL statement.
SELECT
Sqm.SqmId,
Data.value('(/Sqm/Metrics/Metric/id)[1]', 'varchar(max)') as id,
Data.value('(/Sqm/Metrics/Metric/type)[1]', 'varchar(max)') AS type,
Data.value('(/Sqm/Metrics/Metric/unit)[1]', 'varchar(max)') AS unit,
Data.value('(/Sqm/Metrics/Metric/sum)[1]', 'varchar(max)') AS sum,
Data.value('(/Sqm/Metrics/Metric/count)[1]', 'varchar(max)') AS count,
Data.value('(/Sqm/Metrics/Metric/minValue)[1]', 'varchar(max)') AS minValue,
Data.value('(/Sqm/Metrics/Metric/maxValue)[1]', 'varchar(max)') AS maxValue,
Data.value('(/Sqm/Metrics/Metric/stdDeviation)[1]', 'varchar(max)') AS stdDeviation,
FROM Sqm
To me this is much less confusing than using the outer apply or cross apply.
I hope this helps someone else looking for a simpler solution!
Solution 3 - Sql Server
use value
instead of query
(must specify index of node to return in the XQuery as well as passing the sql data type to return as the second parameter):
select
xt.Id
, x.m.value( '@id[1]', 'varchar(max)' ) MetricId
from
XmlTest xt
cross apply xt.XmlData.nodes( '/Sqm/Metrics/Metric' ) x(m)
Solution 4 - Sql Server
I don't understand why some people are suggesting using cross apply
or outer apply
to convert the xml into a table of values. For me, that just brought back way too much data.
Here's my example of how you'd create an xml
object, then turn it into a table.
(I've added spaces in my xml string, just to make it easier to read.)
DECLARE @str nvarchar(2000)
SET @str = ''
SET @str = @str + '<users>'
SET @str = @str + ' <user>'
SET @str = @str + ' <firstName>Mike</firstName>'
SET @str = @str + ' <lastName>Gledhill</lastName>'
SET @str = @str + ' <age>31</age>'
SET @str = @str + ' </user>'
SET @str = @str + ' <user>'
SET @str = @str + ' <firstName>Mark</firstName>'
SET @str = @str + ' <lastName>Stevens</lastName>'
SET @str = @str + ' <age>42</age>'
SET @str = @str + ' </user>'
SET @str = @str + ' <user>'
SET @str = @str + ' <firstName>Sarah</firstName>'
SET @str = @str + ' <lastName>Brown</lastName>'
SET @str = @str + ' <age>23</age>'
SET @str = @str + ' </user>'
SET @str = @str + '</users>'
DECLARE @xml xml
SELECT @xml = CAST(CAST(@str AS VARBINARY(MAX)) AS XML)
-- Iterate through each of the "users\user" records in our XML
SELECT
x.Rec.query('./firstName').value('.', 'nvarchar(2000)') AS 'FirstName',
x.Rec.query('./lastName').value('.', 'nvarchar(2000)') AS 'LastName',
x.Rec.query('./age').value('.', 'int') AS 'Age'
FROM @xml.nodes('/users/user') as x(Rec)
And here's the output: