-- Extract the Event information from the Event Session
WITH results AS (
    SELECT
        event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
        DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],
        event_data.value('(event/data[@name="source_database_id"]/value)[1]', 'int') AS database_id,
        event_data.value('(event/action[@name="session_id"]/value)[1]', 'int') AS [session_id],
        event_data.value('(event/data[@name="cpu"]/value)[1]', 'int') AS [cpu],
        event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS [duration],
        event_data.value('(event/data[@name="reads"]/value)[1]', 'bigint') AS [reads],
        event_data.value('(event/data[@name="writes"]/value)[1]', 'bigint') AS [writes],
        event_data.value('(event/data[@name="state"]/text)[1]', 'nvarchar(4000)') AS [state],
        event_data.value('(event/data[@name="offset"]/value)[1]', 'int') AS [offset],
        event_data.value('(event/data[@name="offset_end"]/value)[1]', 'int') AS [offset_end],
        event_data.value('(event/data[@name="nest_level"]/value)[1]', 'int') AS [nest_level],
        CAST(event_data.value('(event/action[@name="tsql_stack"]/value)[1]', 'nvarchar(4000)') AS XML) AS [tsql_stack],
        REPLACE(event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)'), CHAR(10), CHAR(13)+CHAR(10)) AS [sql_text],
        event_data.value('(event/data[@name="source_database_id"]/value)[1]', 'int') AS [source_database_id],
        event_data.value('(event/data[@name="object_id"]/value)[1]', 'int') AS [object_id],
        event_data.value('(event/data[@name="object_type"]/text)[1]', 'int') AS [object_type],
        CAST(SUBSTRING(event_data.value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50)'), 1, 36) AS uniqueidentifier) as activity_id,
        CAST(SUBSTRING(event_data.value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50)'), 38, 10) AS int) as event_sequence
    FROM
    (
        SELECT XEvent.query('.') AS event_data
        FROM
        (   -- Cast the target_data to XML
            SELECT CAST(target_data AS XML) AS TargetData
            FROM sys.dm_xe_session_targets st
            JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
            WHERE name = 'SQLStmtEvents' AND target_name = 'ring_buffer'
        ) AS Data
        -- Split out the Event Nodes
        CROSS APPLY TargetData.nodes('RingBufferTarget/event') AS XEventData (XEvent)
    ) AS tab (event_data)
)
SELECT DISTINCT
    activity_id,
    db.name as database_name,
    sql_text,
    timestamp,
    reads,
    writes,
    cpu,
    duration / 1000.0 as duration,
    event_sequence,
    event_name
FROM
    results
    LEFT JOIN sys.databases AS db ON db.database_id = results.database_id
WHERE
    db.name IN ('mp','mpstatic','HumanContent','KodDemo')
ORDER BY event_sequence