Return Variable Number Of Attributes From XML As Comma Separated Values

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:

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?

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

StackExchange Database Administrators Q#319262, answer score: 7