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?

Answer 1

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

Answer 2

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

Answer 3

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;

Answer 4

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