8

over-extended

In SQL Server Extended Events for the blocked process report and deadlock XML, it's possible to get multiple SQL Handle values back to identify queries involved in the raised event.

Since 1 or more SQL Handles may be involved, querying the XML reliably to retrieve them all can be difficult, and also makes more straightforward XQuery incorrect, since it only retrieves the first stored value.

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

An example XML fragment for illustration looks like this:

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

A more complete example is available at this SQL Fiddle.

I've gotten as far as this:

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

But that doesn't get what I'm after. Extending that query to use the @sqlhandle attribute throws an error:

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

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

How can I query XML like this to return all listed SQL Handles as a comma separated list?

4 Answers 4

7

Try something like this:

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
0
5

To get over the error message "Attribute may not appear outside of an element" you can use the string function.

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

The above returns a space delimited list but as the SQL handles themselves can't contain spaces in this case you can just use a REPLACE to get the desired comma delimited final result.

Or do

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

Alternatively you can use

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

Or another alternative, without concat, provided by Paul White in the comments

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

The code Erik ended up using in sp_HumanEvents was:

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;
0

Essentially, your issue is that you are returning the actual attribute as a top-level node, which you cannot do. Instead you need to atomize it into a string. You can do this explicitly, but you anyway need concat, which will do it for you.

So you can use the following XQuery syntax
for $x in your/Xquery/path return concat($x/@attributeName, ",")

Furthermore, you should always try to use the / child axis, rather than the // descendant axis, as it's faster.

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

db<>fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.