实战:sqlserver 2012 扩展事件-XML转换为标准的table格式

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:monitortest.xel',metadatafile='s:monitortest.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:monitortest*.xel',-- 's:monitortest*.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(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
    )
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 200 event_data --CAST(event_data AS XML)
FROM sys.fn_xe_file_target_read_file

('s:monitortest*.xel','s:monitortest*.xem',NULL) 

OPEN myCur

FETCH NEXT FROM myCur INTO @xmlString

WHILE @@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_id
			SET @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_text
			SET @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 @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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!