How to query for Xml values and attributes from table in SQL Server?
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
- SQL Server query xml attribute for an element value
- 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 *'
Solution 1:
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
Solution 2:
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:
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:
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:
