How to read and parse the Microsoft SQL Server extended event log file xel file

Question: I'm trying to read the Microsoft SQL Server Extended Event Log File - xel file for the SQL Server Extended Events system_health extended event. 

Just opening it up in Notepad - produces human unreadable output . How can I read the Extended Event Log File output file , using t0-sql?

Answer:  Microsoft have provided various methods and SQL functions which can read the Extended Event Log File file . There are multiple options. It is possible to list out the records within the Log file - this option will return a recordset, as xml output. 

You may want to go one step further - and parse the xml file , to create an output in tabular format. 

Option 1 - Read the Extended Event Log File output file and return as xml format 

SQL Server provide a function - sys.fn_xe_file_target_read_file . This function reads the  files  created by the Extended Events asynchronous file target. There is an event returned by row . The event in the row utilises an XML format

This sample query uses the information gathered in the @path variable to then pass it through to the sys.fn_xe_file_target_read_file function.  Execute this code on a SQL Server and you should see a 2 columns : Event_Data and object_name. In the query below - I'm just checking for 'wait_info'

DECLARE @path NVARCHAR(260);
--retrieve the local path of system_health files 
SELECT @path = dosdlc.path
FROM sys.dm_os_server_diagnostics_log_configurations AS dosdlc;
SET @path = @path + N'system_health_*';

SELECT CAST(fx.event_data AS XML) AS Event_Data,
fx.object_name
FROM sys.fn_xe_file_target_read_file(@path,
NULL,
NULL,
NULL) AS fx
WHERE fx.object_name = 'wait_info';

Option 2 - Read the Extended Event Log File output file and PARSE the returned xml format rows  

This option extends the manipulation of the xml data produced by sys.fn_xe_file_target_read_file . The query below continues to use sys.fn_xe_file_target_read_file but now there is an added CROSS APPLY to the  data nodes 

DECLARE @path NVARCHAR(260);
--retrieve the local path of system_health files 
SELECT @path = dosdlc.path
FROM sys.dm_os_server_diagnostics_log_configurations AS dosdlc;
SET @path = @path + N'system_health_*';
SELECT
    n.value('(@name)[1]', 'varchar(50)') as event_name,
    n.value('(@package)[1]', 'varchar(50)') AS package_name,
    n.value('(@timestamp)[1]', 'datetime2') AS [utc_timestamp],
    n.value('(data[@name="duration"]/value)[1]', 'int') as duration,
    n.value('(data[@name="cpu_time"]/value)[1]', 'int') as cpu,
    n.value('(data[@name="physical_reads"]/value)[1]', 'int') as physical_reads,
    n.value('(data[@name="logical_reads"]/value)[1]', 'int') as logical_reads,
    n.value('(data[@name="writes"]/value)[1]', 'int') as writes,
    n.value('(data[@name="row_count"]/value)[1]', 'int') as row_count,
    n.value('(data[@name="last_row_count"]/value)[1]', 'int') as last_row_count,
    n.value('(data[@name="line_number"]/value)[1]', 'int') as line_number,
    n.value('(data[@name="offset"]/value)[1]', 'int') as offset,
    n.value('(data[@name="offset_end"]/value)[1]', 'int') as offset_end,
    n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') as statement,
    n.value('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') as database_name
FROM(select cast(event_data as XML) as event_data
FROM sys.fn_xe_file_target_read_file(@path, null, null, null)) fx
CROSS APPLY fx.event_data.nodes('event') as q(n)
WHERE n.value('(@package)[1]', 'varchar(50)')  = 'sqlos';

These are some simple examples . If you any other techniques and approaches , let me know 

Read more on XML parsing 

Shredding XML with Powershell and SQL Server (SQL Server DBA)

Troubleshoot SQL Security Error Ring Buffer Recorded - System ...

OpenXML - more than 8000 characters (SQL Server DBA)