How to read extended events .xel file

I have a certain request to read/parse the .xel file which is extended events file , How can I do this effectively and efficiently . One of my colleague advised to use API to parse the file , However I believe there should be a way that this can be achieved through SQL code itself . Help is much appreciated .

回答1:

To read .xel files, you can use sys.fn_xe_file_target_read_file function. For example:

select cast(event_data as XML) as event_data
from sys.fn_xe_file_target_read_file('D:\Folder\MySession*.xel', null, null, null)

Further you may want to parse the returned XML to get the data in table format. To do this, you need to decide what data to extract from the XML and write the appropriate XPath expressions. For example:

-- You have to know element names and their data types
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('D:\Folder\MySession*.xel', null, null, null)) ed
cross apply ed.event_data.nodes('event') as q(n)

回答2:

I had an XEL file from the Auditing system on Azure SQL Database, so I found this post. But it would not run with fn_xe_file_target_read_file, so I had to use fn_get_audit_file like this

select * from sys.fn_get_audit_file('c:\temp\08_11_22_921_16*.xel', null, null)