/*******

  和2008 R2有小区别

*****/

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)
      --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

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