如何从 SQLServer 中的表查询 XML 值和属性？

我有一个包含 Xml列的表:

SELECT *
FROM Sqm

enter image description here

一行的 xml数据样本如下:

<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>

对于这些数据，我希望:

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      ...

最后，我将实际执行 SUM()、 MIN()、 MAX()聚合。但是现在我只是尝试使用一个 xml 列疑问。

在伪代码中，我会尝试这样做:

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

但是这个 SQL 查询不起作用:

味精2396,16层，州1,2号线
XQuery [ Sqm.data.query ()] : 属性不能出现在元素之外

我查找过，令人惊讶的是 XML 查询的文档记录或示例非常糟糕。大多数资源不是查询桌子，而是查询变量; 我没有这样做。大多数资源只使用 xml 查询进行筛选和选择，而不是读取值。大多数资源读取硬编码的子节点(按索引) ，而不是实际值。

我读过的相关资料

更新: . value 而不是. query

我随机使用 .value代替 .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

但这也没有用:

Msg 2389，Level 16，State 1，Line 3 XQuery [ Sqm.data.value ()] :
“ value ()”需要一个单例(或空序列) ，找到操作数为输入‘ xdt: untypedAtomic *’

313502

小开

最佳答案

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

小开

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)

小开

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:

小开

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!