It has been nearly a year since I started an extensive series about Extended Events. Previous to that start, I had already written a bunch of articles about Extended Events.

Among the batch of articles preceding the 60 day series one can find an article about shredding extended event data. Then as a part of the series (the first article in the 60 day series) one can find how to shred the action payload data attached to an extended event session.

You can read the article on shredding XEvent payload data here. Then you can follow that up by reading how to shred the Action data here. And once you are done with those and you really want to learn more about Extended Events, please read the whole series which can be found here.

All of that reading to catch up should keep you busy for a tad bit.

Upgrade

A year is a really long time to go without updating a script – according to some. Today, I have an update for both of the XML shredding scripts you just read about in the list of articles mentioned moments earlier in this post.

Before I dive into the script, let’s revisit a sample of the XML from an event session payload. Within an event session, you can see that there are nodes for both the data and the actions (highlighted in green and red). With all of this information found within the same XML for the session, it somewhat makes sense to try and parse all of the data at once.

In addition to parsing all of the XML for the data and the actions at the same time, it seems to also make sense to generate the statements that would parse the XML within a single effort. As you would have noted, my previous scripts were just that – scripts. That implies executing a separate script for each the data and the actions. Maybe it would make more sense to execute a single script.

It is that notion of a single script that constitutes this upgraded version of the script.

One Script

In the following script, I have tried to accomplish just that – a single script to create the entire XML parser for me, for you, and for anybody wishing to use it. I don’t want to have to remember the subtle nuances of how to parse each of the events each time I need to parse the session data. I want something that is quick, easy, and repeatable.

With all of that said, here is the script that I now use to parse my session data. You should notice that it has been simplified and is more extensive now.

DECLARE @EventName VARCHAR(64) = NULL --'query_post_execution_showplan'--'sp_statement_completed'
,@ReadFlag VARCHAR(64) = 'readonly' --NULL if all columntypes are desired
,@SessionNameVARCHAR(128) = 'AuditDeprecated' --'system_health' --NULL if all Sessions are desired --'Deadlock' --NULL
,@UniqueEventColumnsTINYINT = 1 --1 for unique payload columns across all events within the session, NULL for all columns in session
,@UniqueActions TINYINT = 1 --1 for unique actions, NULL for all actions in the session
/*gen tsql to parse session payload */SELECT '/* parse the data */IF EXISTS (SELECT OBJECT_ID(''tempdb.dbo.#xmlprocess''))
BEGIN
DROP TABLE #xmlprocess
END
SELECT CAST ([t2].[event_data] AS XML) AS event_data, t2.file_offset,t2.file_name, cte1.event_session_id--, '' AS event_predicate
INTO #xmlprocess
FROM ( SELECT REPLACE(CONVERT(NVARCHAR(128),sesf.value),''.xel'',''*.xel'') AS targetvalue, ses.event_session_id
FROM sys.server_event_sessions ses
INNER JOIN sys.server_event_session_fields sesf
ON ses.event_session_id = sesf.event_session_id
--INNER JOIN sys.server_event_session_events sese
--ON ses.event_session_id = sese.event_session_id
WHERE sesf.name = ''filename''
AND ses.name = ''' + @SessionName +'''
) cte1
OUTER APPLY sys.fn_xe_file_target_read_file(cte1.targetvalue,NULL, NULL, NULL) t2
;
SELECT x.event_data.value(''(event/@name)[1]'', ''varchar(50)'') AS event_name
, x.event_data.value(''(event/@package)[1]'', ''varchar(50)'') AS package_name
, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
x.event_data.value(''(event/@timestamp)[1]'',
''datetime2'')) AS [timestamp]';
WITH presel AS (
SELECT oc.OBJECT_NAME AS EventName
,oc.name AS column_name, oc.type_name
,',event_data.value(''(event/data[@name="' + oc.name + '"]/value)[1]'',''' + 
CASE 
WHEN ISNULL(xmv.name,'') = ''
AND oc.type_name = 'guid'
THEN 'uniqueidentifier'
WHEN ISNULL(xmv.name,'') = ''
AND oc.type_name = 'boolean'
THEN 'bit'
WHEN ISNULL(xmv.name,'') = ''
AND oc.type_name = 'callstack'
THEN 'varchar(max)'
WHEN ISNULL(xmv.name,'') = ''
AND oc.type_name <> 'unicode_string'
AND oc.type_name <> 'ansi_string'
AND oc.type_name <> 'ptr'
AND oc.type_name NOT LIKE '%int%'
THEN oc.type_name
WHEN ISNULL(xmv.name,'') = ''
AND oc.type_name LIKE '%int%'
THEN 'bigint'
ELSE 'varchar(max)' END + ''') AS ' + oc.name + '' AS ColumnXML
,oc.column_type AS column_type
,oc.column_value AS column_value
,oc.description AS column_description
,ca.map_value AS SearchKeyword
,sesea.SessionName
,rownum = ROW_NUMBER() OVER (PARTITION BY sesea.SessionName, oc.name ORDER BY oc.object_name)
FROM sys.dm_xe_object_columns oc
-- do we have any custom data types
OUTER APPLY (SELECT DISTINCT mv.name FROM sys.dm_xe_map_values mv
WHERE mv.name = oc.type_name
AND mv.object_package_guid = oc.object_package_guid) xmv
--just get the unique events that are tied to a session on the server (stopped or started state)
CROSS APPLY (SELECT DISTINCT sese.name,ses.name AS SessionName
FROM sys.server_event_session_events sese
INNER JOIN sys.server_event_sessions ses
ON sese.event_session_id = ses.event_session_id) sesea
--keyword search phrase tied to the event
CROSS APPLY (SELECT TOP 1 mv.map_value
FROM sys.dm_xe_object_columns occ
INNER JOIN sys.dm_xe_map_values mv
ON occ.type_name = mv.name
AND occ.column_value = mv.map_key
WHERE occ.name = 'KEYWORD'
AND occ.object_name = oc.object_name) ca
WHERE oc.column_type <> @ReadFlag
AND sesea.name = oc.object_name
AND oc.object_name = ISNULL(@EventName,oc.object_name)
AND sesea.SessionName = ISNULL(@SessionName,sesea.SessionName)
)
SELECT p.EventName
 , p.column_name
 , p.type_name
 , p.ColumnXML
 , p.column_type
 , p.column_value
 , p.column_description
 , p.SearchKeyword
 , p.SessionName
FROM presel p
WHERE p.rownum = ISNULL(@UniqueEventColumns,p.rownum)
ORDER BY p.SessionName,p.EventName
;
/* auto generate the xml associated to an event session action deployed to the server */WITH presel AS (
SELECT p.name AS package_name
        ,o.name AS action_name
,',event_data.value(''(event/action[@name="' + esa.name + '"]/value)[1]'', ''' + 
CASE 
WHEN o.type_name = 'guid'
THEN 'uniqueidentifier'
WHEN o.type_name = 'boolean'
THEN 'bit'
WHEN o.type_name = 'binary_data'
THEN 'varbinary(max)'
WHEN o.type_name = 'callstack'
THEN 'varbinary(max)'
WHEN o.type_name = 'filetime'
THEN 'varbinary(max)'
WHEN o.type_name = 'cpu_cycle'
THEN 'varbinary(max)'
WHEN ISNULL(o.type_name,'') = ''
THEN NULL
WHEN o.type_name <> 'unicode_string'
AND o.type_name <> 'ansi_string'
AND o.type_name <> 'ptr'
AND o.type_name NOT LIKE '%int%'
THEN o.type_name
WHEN o.type_name LIKE '%int%'
THEN 'int'
ELSE 'varchar(max)' END + ''') AS ' + esa.name +'' AS ActionXML
,ses.name AS EventSessionName
, ese.name AS EventName
        ,o.description
, rownum = ROW_NUMBER() OVER (PARTITION BY ses.name,o.name ORDER BY ese.name)
FROM sys.dm_xe_packages AS p
INNER JOIN sys.dm_xe_objects AS o
ON p.guid = o.package_guid
INNER JOIN sys.server_event_session_actions esa
ON o.name = esa.name
INNER JOIN sys.server_event_sessions ses
ON esa.event_session_id = ses.event_session_id
INNER JOIN sys.server_event_session_events ese
ON esa.event_session_id = ese.event_session_id
AND ese.event_id = esa.event_id
WHERE o.object_type = 'action'
AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
AND (p.capabilities IS NULL OR p.capabilities & 1 = 0)
AND ese.name = ISNULL(@EventName,ese.name)
AND ses.name = ISNULL(@SessionName,ses.name)
)
SELECT p.package_name
 , p.action_name
 , p.ActionXML
 , p.EventSessionName
 , p.EventName
 , p.description
FROM presel p
WHERE p.rownum = ISNULL(@UniqueActions,p.rownum)
ORDER BY p.EventSessionName, p.action_name, p.EventName
;
/* gen last piece of xml parsing sql code block*/SELECT 'FROM #xmlprocess x
LEFT OUTER JOIN sys.server_event_session_events sese
ON x.event_data.value(''(event/@name)[1]'', ''varchar(50)'') = sese.name
AND x.event_session_id = sese.event_session_id
ORDER BY timestamp
, event_data.value(''(event/action[@name="event_sequence"]/value)[1]'',
''varchar(max)'');'

Not only does this script slice and dice for you…Wait that’s not quite right.

This script doesn’t just generate the XML shredding strings for the payload and action data. This script also will produce the rest of the tsql statements that should precede and conclude the XML parsing statements. Yes it uses dynamic sql. That is to your advantage in this case.

Could there be other enhancements? Absolutely! More will be coming.

Enjoy this script and happy parsing.

Extra Extra – Read All About It!

From the comments, you will see that Brent Ozar (blog | twitter) made an excellent suggestion. To be honest, I considered sharing some examples during the initial write of this article. I had opted against it then, not considering it in the same way that Brent puts it. So, in this addendum I have a quick example using an XEvent Session that I have not yet written about in all of my articles on Extended Events (pseudo spoiler alert).

Let’s use the AuditSelects XEvent Session I have deployed and running (remember a session can be deployed but may be in the “stopped” state). If I want to figure out what data has been captured for this session, I can take the session name and plug it into the script shown in this article.

Ignore the green tsql comments for now (you can read them direct from the script). Highlighted in gold is the variable for the session name. By entering a valid session name for this variable, I can restrict the results to just the metadata for that specific session. In addition, note that I have highlighted, in a pale chartreuse, two variables that will give me unique column names for each of the events and actions within each session. This is important because some events have the same name for different attributes as other events. If events with the same attribute names are used in the same session, you will get a duplication of data. Maybe you want the duplicate data. Maybe you don’t.

The session name variable is used in each of the next three code segments. Each segment in the script will generate sql statements that will need to be copied into a new query window. I will leave the further breakdown of the script as an exercise for you. What I want to do here is show the results from executing this script.

When I execute the script, I will have four result sets returned to me (in grid mode). It will look something like shown in the following image.

Breaking this down into three colors is probably easiest to see how things fit together. Highlighted in red, I have the build out of the pre-processing statements and the build of the “select” statement that will be returning our data to us. Then in blue comes the guts of the query – all of the beautiful XML parsing statements. The segments in blue correlates to the columns in the select list. And at the bottom of the image I have the finishing pieces that includes my predicates, from, and joins. Each of the highlighted segments will then be copied and pasted to a new query window and look something like the following.

And there you have it. Sure there are still some manual steps in it, but you no longer need to memorize all of that glorious XML parsing syntax. You only need to copy and paste with this version of the script.

Enjoy your adventures in parsing XML!