IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='test')
    DROP EVENT SESSION [test] ON SERVER;
CREATE EVENT SESSION [test]
ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
     ACTION (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_id, sqlserver.plan_handle, sqlserver.session_id, sqlserver.sql_text, sqlserver.username)
     WHERE (([sqlserver].[username]='test'))),
ADD EVENT sqlserver.sp_statement_completed(
     ACTION (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_id, sqlserver.plan_handle, sqlserver.session_id, sqlserver.sql_text, sqlserver.username)
     WHERE (([sqlserver].[username]='test')))
ADD TARGET package0.asynchronous_file_target(
     SET filename='c:\temp\test.xel')
WITH (MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 300 SECONDS, MAX_EVENT_SIZE = 0KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF)

ALTER EVENT SESSION [test] ON SERVER STATE = START

DECLARE @path nvarchar(260), @mdpath nvarchar(260), @xesession nvarchar(50)
set @xesession = 'test'

-- Get the log file name and substitute * wildcard in
SELECT 
    @path = LEFT(column_value, LEN(column_value)-CHARINDEX('.', REVERSE(column_value))) 
        + '*' 
        + RIGHT(column_value, CHARINDEX('.', REVERSE(column_value))-1)
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_object_columns soc
    ON s.address = soc.event_session_address
WHERE s.name = @xesession
  AND soc.object_name = 'asynchronous_file_target'
  AND soc.column_name = 'filename'

-- Get the metadata file name and substitute * wildcard in 
SELECT 
    @mdpath = LEFT(column_value, LEN(column_value)-CHARINDEX('.', REVERSE(column_value))) 
        + '*' 
        + RIGHT(column_value, CHARINDEX('.', REVERSE(column_value))-1)
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_object_columns soc
    ON s.address = soc.event_session_address
WHERE s.name = @xesession
  AND soc.object_name = 'asynchronous_file_target'
  AND soc.column_name = ' metadatafile'

-- Set the metadata filename if it is NULL to the log file name with xem extension
SELECT @mdpath = ISNULL(@mdpath, 
                        LEFT(@path, LEN(@path)-CHARINDEX('*', REVERSE(@path))) 
                        + '*xem')

select 
DATEADD(hh, 
            DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
            n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
n.value('(action[@name="session_id"]/value)[1]','int') as session_id,
n.value('(data[@name="duration"]/value)[1]','int') as duration,
n.value('(data[@name="reads"]/value)[1]','int') as reads,
n.value('(data[@name="writes"]/value)[1]','int') as writes,
n.value('(action[@name="client_app_name"]/value)[1]','varchar(255)') as client_app_name,
n.value('(action[@name="client_hostname"]/value)[1]','varchar(255)') as client_hostname,
n.value('(action[@name="database_id"]/value)[1]','int') as database_id,
n.value('(action[@name="username"]/value)[1]','varchar(255)') as username,
n.value('(action[@name="sql_text"]/value)[1]','varchar(max)') as sql_text,
n.value('(action[@name="plan_handle"]/value)[1]','varchar(max)') as plan_handle
 from
(select CAST(event_data as XML) as event_data
from sys.fn_xe_file_target_read_file(@path,@mdpath,null,null)) as tab
cross apply event_data.nodes('event') as q(n)