Return Variable Number Of Attributes From XML As Comma Separated Values

Question

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?

David Browne - Microsoft · Accepted Answer · 2022-11-06 19:58:36Z

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

answered Nov 6, 2022 at 19:58

David Browne - Microsoft

49.2k3 gold badges53 silver badges102 bronze badges

Add a comment |

Martin Smith · Accepted Answer · 2022-11-07 08:32:30Z

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)'
    );

Paul White · Accepted Answer · 2022-11-08 03:16:35Z

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;

Charlieface · Accepted Answer · 2022-11-07 16:00:09Z

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

Stack Exchange Network

Return Variable Number Of Attributes From XML As Comma Separated Values

over-extended

4 Answers 4

Your Answer

Hot Network Questions

Return Variable Number Of Attributes From XML As Comma Separated Values

over-extended

4 Answers 4

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions