我有一个包含Xml
列的表:
SELECT *
FROM Sqm
某一行的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
但这也不起作用:
消息2389,级别16,状态1,第3行XQuery Sqm.data.value():
“‘value()”需要单个(或空序列),找到的操作数的类型为“xdt:untypedAtom*”
发布于 2016-12-14 00:52:00
我一直在尝试做一些非常类似的事情,但没有使用节点。但是,我的xml结构略有不同。
它是这样的:
<Metrics>
<Metric id="TransactionCleanupThread.RefundOldTrans" type="timer" ...>
如果是这样的话:
<Metrics>
<Metric>
<id>TransactionCleanupThread.RefundOldTrans</id>
<type>timer</type>
.
.
.
然后,您可以简单地使用此SQL语句。
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
对我来说,这比使用外部应用或交叉应用要容易得多。
我希望这对其他正在寻找更简单解决方案的人有所帮助!
发布于 2013-10-04 01:24:28
使用value
而不是query
(必须指定要在XQuery中返回的节点的索引,并传递要作为第二个参数返回的sql数据类型):
select
xt.Id
, x.m.value( '@id[1]', 'varchar(max)' ) MetricId
from
XmlTest xt
cross apply xt.XmlData.nodes( '/Sqm/Metrics/Metric' ) x(m)
发布于 2016-09-08 16:48:13
我不明白为什么有人建议使用cross apply
或outer apply
将xml转换为值表。对我来说,这只是带回了太多的数据。
下面是我的示例,演示如何创建一个xml
对象,然后将其转换为表。
(我在xml字符串中添加了空格,只是为了使其更易于阅读。)
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)
下面是输出:
https://stackoverflow.com/questions/19165213
复制相似问题