Actual combat: SQL Server 2012 extended event-xml converted to standard table format

/*******

There's a small difference between R2 and

*****/

CREATE EVENT SESSION [test] on SERVER
ADD EVENT sqlserver.sql_statement_completed
(SET collect_statement= (1)
ACTION
(
Sqlserver.client_app_name,
Sqlserver.client_hostname,
SQLSERVER.DATABASE_ID,
Sqlserver.database_name,
Sqlserver.sql_text,sqlserver.username
)
WHERE ([cpu_time]> (10000))
)
ADD TARGET Package0.asynchronous_file_target
(
SET filename= ' S:\monitor\test.xel ',
Metadatafile= ' S:\monitor\test.xem '
)
With
(
max_memory=1048576 KB,
Startup_state=on
)
GO

--select *, CAST (Event_data as XML) as ' Event_data_xml '
--from Sys.fn_xe_file_target_read_file
--(
--' s:\monitor\test*.xel ',
--' s:\monitor\test*.xem ', NULL, NULL
-- );

IF EXISTS (SELECT *
From Tempdb.dbo.sysobjects
WHERE id = object_id (N ' tempdb: #MyData ')
and type = ' U ')
DROP TABLE #MyData
Go

CREATE TABLE #MyData
(
database_id INT not NULL,
Username NVARCHAR (+) not NULL,
Client_hostname NVARCHAR (+) not NULL,
Client_app_name NVARCHAR (+) not NULL,
Sql_text NVARCHAR (MAX) is not NULL,
Cpu_time INT not NULL
)
Go

DECLARE @xmlData XML
DECLARE @xmlString NVARCHAR (MAX)
DECLARE @database_id INT
DECLARE @username NVARCHAR (100)
DECLARE @client_hostname NVARCHAR (100)
DECLARE @client_app_name NVARCHAR (100)
DECLARE @sql_text NVARCHAR (MAX)
DECLARE @cpu_time INT

DECLARE mycur CURSOR read_only
For
SELECT TOP event_data--cast (event_data as XML)
From Sys.fn_xe_file_target_read_file

(' S:\monitor\test*.xel ',
' S:\monitor\test*.xem ', null,null)

OPEN Mycur

FETCH NEXT from Mycur to @xmlString

While @ @FETCH_STATUS = 0

BEGIN
BEGIN TRY
SET @xmlData = CAST (@xmlString as XML)
--set @cpu = 0
--Get CPU
SET @cpu_time = @xmlData. Query ('//data[@name = ' cpu_time ']/value '). Value (' (value) [1] ',
' INT ')

--Get database_id
SET @database_id = @xmlData. Query ('//action[@name = ' database_id ']/value '). Value (' (value) [1] ',
' INT ')
--Get username
SET @username = @xmlData. Query ('//action[@name = ' username ']/value '). Value (' (value) [1] ',
' NVARCHAR (100) ')
--Get hostname
SET @client_hostname = @xmlData. Query ('//action[@name = ' client_hostname ']/value '). Value (' (value) [1] ',
' NVARCHAR (100) ')

--Get Client_app_name
SET @client_app_name = @xmlData. Query ('//action[@name = ' client_app_name ']/value '). Value (' (value) [1] ',
' NVARCHAR (100) ')

--Get Sql_text
SET @sql_text = @xmlData. Query ('//action[@name = ' sql_text ']/value '). Value (' (value) [1] ',
' NVARCHAR (MAX) ')

--Start inserting data
INSERT #MyData
(database_id,
Sql_text,
Username
Client_hostname,
Client_app_name,
Cpu_time)
VALUES (@database_id,--Database_id-int
@sql_text,--Sql_text-nvarchar (max)
@username,
@client_hostname,
@client_app_name,
@cpu_time
)
END TRY
BEGIN CATCH
END CATCH


FETCH NEXT from Mycur to @xmlString
END
CLOSE Mycur
Deallocate mycur

SELECT B.name,a.username,a.client_hostname,a.client_app_name,a.sql_text,a.cpu_time from #MyData as a
INNER JOIN sys.databases as B
On a.database_id=b.database_id
ORDER BY a.cpu_time Desc
Go

Actual combat: SQL Server 2012 extended event-xml converted to standard table format