首页 > 代码库 > 实战:sqlserver 2012 扩展事件-XML转换为标准的table格式
实战: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:\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(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:\monitor\test*.xel', 's:\monitor\test*.xem',NULL,NULL) OPEN myCur FETCH NEXT FROM myCur INTO @xmlString WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY SET @xmlData = http://www.mamicode.com/CAST(@xmlString AS XML)>实战:sqlserver 2012 扩展事件-XML转换为标准的table格式
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。