我有一个表,其中包含Xml
column:
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()
聚合。但现在我只是想query一个 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 查询的文档或示例非常少。大多数资源而不是查询table,查询一个variable;我没有这样做。大多数资源仅使用 xml 查询进行过滤和选择,而不是读取值。大多数资源读取硬编码的子节点(按索引),而不是实际值。
我读过的相关资源
- https://stackoverflow.com/questions/966441/xml-query-in-sql-server-2008
- SQL Server 查询元素值的 xml 属性
- SQL 查询 XML 属性
- SQL Server 2005 XQuery 和 XML-DML - 第 1 部分
- BOL:Microsoft SQL Server 2005 中的 XML 支持
- 在 SQL Server 中查询 XML
- 基本 SQL Server XML 查询
- BOL:query() 方法(xml 数据类型)
- XML 研讨会 V - 从 XML 列读取值
- SQL SERVER – 发现 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:untypedAtomic *'