實戰:sqlserver 2012 擴充事件-XML轉換為標準的table格式

標籤：sql server extended   sqlserver 2012 擴充事件   

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 #MyDatagoCREATE TABLE #MyData    (      database_id INT NOT NULL ,      username NVARCHAR(100) NOT NULL,      client_hostname NVARCHAR(100) NOT NULL,  client_app_name NVARCHAR(100) NOT NULL,      sql_text NVARCHAR(MAX) NOT NULL ,      cpu_time INT NOT NULL    )goDECLARE @xmlData XMLDECLARE @xmlString NVARCHAR(MAX)DECLARE @database_id INTDECLARE @username NVARCHAR(100)DECLARE @client_hostname NVARCHAR(100)DECLARE @client_app_name NVARCHAR(100) DECLARE @sql_text NVARCHAR(MAX)DECLARE @cpu_time INTDECLARE myCur CURSOR READ_ONLYFORSELECT TOP 200 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 myCurFETCH NEXT FROM myCur INTO @xmlStringWHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY    SET @xmlData = CAST(@xmlString AS XML)    --set @cpu = 0    --擷取cpu  SET @cpu_time = @xmlData.query('//data[@name="cpu_time"]/value').value('(value)[1]',  'INT')        --擷取database_idSET @database_id = @xmlData.query('//action[@name="database_id"]/value').value('(value)[1]',  'INT')--擷取username              SET @username = @xmlData.query('//action[@name="username"]/value').value('(value)[1]',  'NVARCHAR(100)')--擷取hostname              SET @client_hostname = @xmlData.query('//action[@name="client_hostname"]/value').value('(value)[1]',  'NVARCHAR(100)')--擷取client_app_name              SET @client_app_name = @xmlData.query('//action[@name="client_app_name"]/value').value('(value)[1]',  'NVARCHAR(100)')  --擷取sql_textSET @sql_text = @xmlData.query('//action[@name="sql_text"]/value').value('(value)[1]',  'NVARCHAR(MAX)')--開始插入資料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 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 join sys.databases as bon a.database_id=b.database_idorder by a.cpu_time descgo

實戰:sqlserver 2012 擴充事件-XML轉換為標準的table格式