主页 / dba / 问题 / 319262
Accepted
Erik Darling
Asked: 2022-11-07 11:23:21 +0800 CST

从 XML 中以逗号分隔值的形式返回可变数量的属性

  • 772

过度扩张

在阻塞进程报告和死锁 XML 的 SQL Server 扩展事件中，可以获取多个 SQL 句柄值以识别引发事件中涉及的查询。

由于可能涉及到一个或多个 SQL 句柄，因此可靠地查询 XML 以检索它们可能很困难，并且还会使更直接的 XQuery 不正确，因为它只检索第一个存储的值。

sqlhandle = bd.value('(process/executionStack/frame/@sqlhandle)[1]', 'varchar(130)'),

用于说明的示例 XML 片段如下所示：

<executionStack>
      <frame line="1" stmtend="108" sqlhandle="0x020000008d18260040e407ba48fc247b0cb6121c21c2cf2b0000000000000000000000000000000000000000" />
      <frame line="1" stmtend="108" sqlhandle="0x02000000dd847b18dcaa4a09a89f56595186fcf91da8a7f70000000000000000000000000000000000000000" />
</executionStack>

此 SQL Fiddle提供了更完整的示例。

我已经做到了：

SELECT 
    sql_handle = 
        @x.query('for $s in //executionStack/frame return $s');

但这并没有得到我所追求的。扩展该查询以使用该@sqlhandle属性会引发错误：

SELECT 
    sql_handle = 
        @x.query('for $s in //executionStack/frame/@sqlhandle return $s');

消息 2396，级别 16，状态 1，第 60 行 XQuery [query()]：属性可能不会出现在元素之外

如何像这样查询 XML 以将所有列出的 SQL 句柄作为逗号分隔列表返回？

sql-server

4 个回答

  1. Best Answer

    尝试这样的事情：

    SELECT x.d.value('@name','varchar(200)') name, f.sqlhandles
FROM @x.nodes('//data') x(d)
CROSS APPLY 
(
  SELECT string_agg(n.f.value('@sqlhandle','varchar(200)'),',') sqlhandles
  from x.d.nodes('.//executionStack/frame') n(f)
 ) f
    • 7

  2. 要克服错误消息“属性可能不会出现在元素之外”，您可以使用该string函数。

     SELECT @x.query('
                for $s in  //process/executionStack/frame/@sqlhandle
                return string($s)  
                ').value('.', 'varchar(max)')

    上面返回一个空格分隔的列表，但由于 SQL 句柄本身不能包含空格，在这种情况下，您可以只使用 aREPLACE来获得所需的逗号分隔的最终结果。

    或者做

     SELECT @x.query('
                for $s in  //process/executionStack/frame/@sqlhandle
                return concat(",",string($s))
                ').value('substring(./text()[1], 2)', 'varchar(max)')

    或者，您可以使用

    SELECT REPLACE(
    @x.query('data(//process/executionStack/frame/@sqlhandle)').value('.', 'varchar(max)'), 
    ' ',
    ',')

    concat或Paul White 在评论中提供的另一种替代方案

    SELECT 
    sql_handles = 
    @x.query
    (
        '
        for $h in (//executionStack/frame/@sqlhandle)
        return 
        (
            ",", 
            string($h)
        )
        '
    ).value
    (
        'substring(./text()[1], 3)', 
        'nvarchar(max)'
    );
    • 5

  3. Erik 最终使用的代码sp_HumanEvents 是：

    CROSS APPLY 
(
    SELECT 
        sql_handles = 
            STUFF
            (
                (
                    SELECT DISTINCT
                        ',' +
                        RTRIM
                        (
                            n.c.value('@sqlhandle', 'varchar(130)')
                        )
                    FROM kheb.blocked_process_report.nodes('//executionStack/frame') AS n(c)
                    FOR XML
                        PATH(''),
                        TYPE
                ).value('./text()[1]', 'varchar(max)'),
                1,
                1,
                ''
            )                    
) AS c;
    • 2

  4. 本质上，您的问题是您将实际属性作为顶级节点返回，这是您无法做到的。相反，您需要将其原子化为字符串。您可以明确地执行此操作，但无论如何您都需要concat，它会为您执行此操作。

    因此您可以使用以下 XQuery 语法
    for $x in your/Xquery/path return concat($x/@attributeName, ",")

    此外，您应该始终尝试使用/子轴，而不是//后代轴，因为它更快。

    SELECT 
  sql_handle =  @x.query('
    for $x in /event/data/value/blocked-process-report/blocked-process/process/executionStack/frame
    return concat($x/@sqlhandle, ",")
    ');

    db<>小提琴

    • 0

相关问题