如何从 SQL Server 中的表中查询 Xml 值和属性?

2022-01-16 00:00:00 xml sql-server-2005 sql-server sql-server-2008-r2 sqlxml

我有一个包含 Xml 列的表:

I have a table that contains a Xml column:

SELECT * 
FROM Sqm

一行的 xml 数据示例如下:

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

最后我将实际执行 SUM()、MIN()、MAX() 聚合.但现在我只是想查询一个 xml 列.

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

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

But that SQL query doesn't work:

消息 2396，第 16 级，状态 1，第 2 行
XQuery [Sqm.data.query()]:属性可能不会出现在元素之外

Msg 2396, Level 16, State 1, Line 2
XQuery [Sqm.data.query()]: Attribute may not appear outside of an element

我一直在寻找，令人惊讶的是，Xml 查询的文档或示例如此糟糕.大多数资源不是查询一个表，而是查询一个变量；我没有这样做.大多数资源只使用 xml 查询进行过滤和选择，而不是读取值.大多数资源读取硬编码的子节点(按索引)，而不是实际值.

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.

  • 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 Workshop V - 阅读价值观来自 XML 列
  • SQL SERVER - 发现 XML 数据类型方法简介 - 入门

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

但这也行不通:

消息 2389，级别 16，状态 1，行 3 XQuery [Sqm.data.value()]:
'value()' 需要一个单例(或空序列)，找到的操作数输入'xdt:untypedAtomic *'

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

推荐答案

其实你已经接近你的目标了，你只需要使用 nodes() 方法来拆分行然后获取值:

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

相关文章