Início / dba / Perguntas / 319262
Accepted
Erik Darling
Asked: 2022-11-07 11:23:21 +0800 CST

Retornar o número variável de atributos do XML como valores separados por vírgula

  • 772

estendido demais

No SQL Server Extended Events para o relatório de processo bloqueado e XML de deadlock, é possível obter vários valores de SQL Handle de volta para identificar as consultas envolvidas no evento gerado.

Uma vez que 1 ou mais SQL Handles podem estar envolvidos, consultar o XML de forma confiável para recuperá-los pode ser difícil, e também torna a XQuery mais direta incorreta, pois ela apenas recupera o primeiro valor armazenado.

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

Um exemplo de fragmento XML para ilustração se parece com isto:

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

Um exemplo mais completo está disponível neste SQL Fiddle .

cheguei até aqui:

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

Mas isso não é o que eu procuro. Estender essa consulta para usar o @sqlhandleatributo gera um erro:

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

Msg 2396, Level 16, State 1, Line 60 XQuery [query()]: o atributo não pode aparecer fora de um elemento

Como posso consultar XML como este para retornar todos os identificadores SQL listados como uma lista separada por vírgulas?

sql-server

4 respostas

  1. Best Answer

    Tente algo assim :

    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. Para superar a mensagem de erro "O atributo pode não aparecer fora de um elemento", você pode usar a stringfunção.

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

    O acima retorna uma lista delimitada por espaço, mas como os próprios identificadores SQL não podem conter espaços, neste caso, você pode apenas usar a REPLACEpara obter o resultado final delimitado por vírgula desejado.

    Ou faça

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

    Alternativamente, você pode usar

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

    Ou outra alternativa, sem concat, fornecida por Paul White nos comentários

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

  3. O código que Erik acabou usando sp_HumanEvents foi :

    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. Essencialmente, seu problema é que você está retornando o atributo real como um nó de nível superior, o que você não pode fazer. Em vez disso, você precisa atomizá-lo em uma string. Você pode fazer isso explicitamente, mas de qualquer maneira precisa concatde , que fará isso por você.

    Então você pode usar a seguinte sintaxe XQuery
    for $x in your/Xquery/path return concat($x/@attributeName, ",")

    Além disso, você deve sempre tentar usar o /eixo filho, em vez do //eixo descendente, pois é mais rápido.

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

    db<>violino

    • 0

relate perguntas