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

CREATE event SESSION [test] on SERVER ADD EVENT sqlserver.sql_statement_completed (SET collect_statement= (1) ACTION (sq Lserver.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 Filenam E= ' 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 #MyDatagoCREATE table #MyData (database_id INT not N ULL, username NVARCHAR (+) NOT NULL, Client_hostname NVARCHAR (+) NOT NULL, Client_app_name NVARCHAR (+) NO    T NULL,  Sql_text NVARCHAR (max) not NULL, Cpu_time int. NOT NULL) Godeclare @xmlData xmldeclare @xmlString NVARCHAR (max) D Eclare @database_id intdeclare @username NVARCHAR DECLARE @client_hostname NVARCHAR (+) DECLARE @client_app_name NVARCHAR (+) DECLARE @sql_text NVARCHAR (MAX) DECLARE @cpu_time intdeclare mycur CURSOR read_onlyforselect TOP event_d ATA--cast (Event_data as XML) from Sys.fn_xe_file_target_read_file (' S:\monitor\test*.xel ', ' S:\monitor\test*.xem ', Null,null) OPEN Mycurfetch NEXT from mycur to @xmlStringWHILE @ @FETCH_STATUS = 0 BEGIN begin TRY SET @xmlData = CAST ( @xmlString as XML)--set @cpu = 0-Gets the CPU set @cpu_time = @xmlData. Query ('//data[@name = ' cpu_time ']/value '). Value (' ( Value) [1] ', ' INT ')--get database_idset @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] ', ' NVAR CHAR (100) ')--get client_app_name SET @client_app_name = @xmlData. Query ('//action[@name = ' Client_app_name ']/value '). Value (' (value) [1] ', ' NVARCHAR (100) ')--get sql_textset @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) @use  Rname, @client_hostname, @client_app_name, @cpu_time) end TRY BEGIN catch END catch FETCH NEXT from Mycur into @xmlStringENDCLOSE mycurdeallocate Mycurselect B.name,a.username,a.client_hostname,a.client_app_name,a.sql_text, A.cpu_time from #MyData as Ainner joins sys.databases as Bon A.database_id=b.database_idorder by A.cpu_time Descgo

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