SELECT [n].[value] ('(@name)[1]', 'varchar(50)') AS [event_name]
,[n].[value] ('(data[@name="batch_text"]/value)[1]', 'nvarchar(max)') AS batch_text
,[n].[value] ('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') AS [database_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]', 'BIGINT') AS [duration]
,[n].[value] ('(data[@name="cpu_time"]/value)[1]', 'BIGINT') AS [cpu]
,[n].[value] ('(data[@name="physical_reads"]/value)[1]', 'BIGINT') AS [physical_reads]
,[n].[value] ('(data[@name="logical_reads"]/value)[1]', 'BIGINT') AS [logical_reads]
,[n].[value] ('(data[@name="writes"]/value)[1]', 'BIGINT') AS [writes]
,[n].[value] ('(data[@name="row_count"]/value)[1]', 'BIGINT') AS [row_count]
,[n].[value] ('(data[@name="last_row_count"]/value)[1]', 'BIGINT') AS [last_row_count]
,[n].[value] ('(data[@name="line_number"]/value)[1]', 'BIGINT') AS [line_number]
,[n].[value] ('(data[@name="offset"]/value)[1]', 'BIGINT') AS [offset]
,[n].[value] ('(data[@name="offset_end"]/value)[1]', 'BIGINT') 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([fn_xe_file_target_read_file].[event_data] AS XML) AS [event_data]
FROM [sys].fn_xe_file_target_read_file ('D:\Extended Event File Logs\LightPerformanceEvents*.xel', NULL, NULL, NULL)
) [ed]
CROSS APPLY [ed].[event_data].nodes ('event') AS [q]([n])
WHERE [n].[value] ('(@timestamp)[1]', 'datetime2') > DATEADD(day, -1, GETDATE())
--AND [n].[value] ('(@name)[1]', 'varchar(50)') = 'sql_batch_completed'
--AND (
--[n].[value] ('(data[@name="batch_text"]/value)[1]', 'nvarchar(max)') LIKE 'select Temp5.ChildLocationId%' --OR
--[n].[value] ('(data[@name="batch_text"]/value)[1]', 'nvarchar(max)') LIKE 'FLSP_CHILDLOCATION_DASHBOARD_DYMAMIC%' or
--
--[n].[value] ('(data[@name="batch_text"]/value)[1]', 'nvarchar(max)') LIKE '%APP_LOGS%'
--)
ORDER BY utc_timestamp DESC
