I love Extended Events. I hate XML. I love the dynamic shred for XML. I hate having to write XML code when I can have it write itself.
Here's an easy(er) way to deal with XE XML.
This XE is specifically to find queries with a duration longer than 1 second.
How, you ask?
We create an Extended Event for items of long-running duration.
We then feed it into a dynamic XML shredder ('local-name(.)'), that kind of thing.
We then take THOSE results and feed it into a Dynamic Pivot.
no crappy code that looks like:
event_data_XML.value('(event/data)','INT') AS object_type,
event_data_XML.value('(event/data)','INT') AS cpu,
event_data_XML.value('(event/data)','INT') AS duration,