System_Health dynamic parser - one table

system_health_parser_one_table

if object_id('tempdb..#systemhealthsessiondata') is not null
    DROP TABLE #systemhealthsessiondata

SELECT CAST(xet.target_data AS XML) AS XMLDATA
INTO #SystemHealthSessionData
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'system_health'

if object_id('tempdb..#ParsedData') is not null
    DROP TABLE #ParsedData
CREATE TABLE #ParsedData (id INT IDENTITY, Actual_Time DATETIME, EventType NVARCHAR(128), ParsedName NVARCHAR(128), NodeValue VARCHAR(MAX))

;WITH CTE_HealthSession (EventXML) AS
(
SELECT C.query('.') EventXML
FROM #SystemHealthSessionData a
CROSS APPLY a.XMLDATA.nodes('/RingBufferTarget/event') as T(C)
--WHERE C.query('.').value('(/event/@name)[1]', 'varchar(255)') in ('wait_info','wait_info_external')
)
INSERT INTO #ParsedData (Actual_Time, EventType, ParsedName, NodeValue)--(id, ParsedName, NodeValue)
SELECT	--id,
			DATEADD(MINUTE, DATEPART(TZoffset, SYSDATETIMEOFFSET()), UTC_Time) AS Actual_Time,
			EventType,
			ParsedName,
			NodeValue
	FROM	(
			 SELECT	
					A.B.value('@name[1]', 'varchar(128)') AS EventType,
					A.B.value('./@timestamp[1]', 'datetime') AS UTC_Time,
					X.N.value('local-name(.)', 'varchar(128)') AS NodeName,
					X.N.value('../@name[1]', 'varchar(128)') AS ParsedName,
					X.N.value('./text()[1]', 'varchar(max)') AS NodeValue
			 FROM	cte_healthsession
			 CROSS APPLY EventXML.nodes('/*') AS A (B)
			 CROSS APPLY EventXML.nodes('//*') AS X (N)
			) T
	--WHERE	NodeName = 'value' 
	--AND event_type = 'wait_info'

--you can run this entire next batch separately 
 
--And now use the standard dynamic pivot to shred. 
-- Because of the way the pivot works, the fields are alphabetical; not a big deal, but not easily fixable when running multiserver
  DECLARE @SQL AS VARCHAR (MAX)
  DECLARE @Columns AS VARCHAR (MAX)
  DECLARE @min INT, @max INT, @eventtype VARCHAR(128)

		SET @Columns = NULL 
		SET @SQL = NULL

		  SELECT @Columns=
				 COALESCE(@Columns + ',','') + QUOTENAME(ParsedName)
		  FROM
		  (
				 SELECT DISTINCT ParsedName
				 FROM #ParsedData 
				 --excluded it here, but the tsql_stack can be used to get the exact statement from the plan cache
				 --see http://blogs.msdn.com/b/extended_events/archive/2010/05/07/making-a-statement-how-to-retrieve-the-t-sql-statement-that-caused-an-event.aspx 
				 WHERE ParsedName <> 'callstack'
		  ) AS B
		  ORDER BY B.ParsedName
  
		  SET @SQL='
		  SELECT Actual_Time, EventType,' + @Columns + '  FROM 
		  (
		  SELECT EventType, Actual_Time, ParsedName, NodeValue FROM 
		  #ParsedData) AS source 
		  PIVOT
		  (max(NodeValue) FOR source.ParsedName IN (' + @columns + ')
		  )AS pvt order by actual_time'--, attach_activity_id'

		  PRINT @sql
		  EXEC (@sql)