Extended Events with a view

So last time I wrote, I talked about my interest in Extended Events; I have been playing with them a bit since then and I came across something that made them so much easier to work with. This is not my own idea, but I wanted to share it as it blew my mind away with how simple it is, yet how I never thought of it. The idea is to take your ugly XQuery code and put it in a view, and to make it even better, create a Schema just for your Extended Events. I created a Schema called "XE" then when I want to get data from an Extended Event, I just have to type SELECT * FROM [XE]. and I let IntelliSense or SQLPrompt fill out the available options. This essentially makes querying Extended Events as easy as using a DMV. So in my last post, we created a Extended Even called "XE_Log_Shrink", If I wanted to implement this idea for this XE, I would do the following: Create the Schema (if I haven't previously done this):

USE [master]
GO
CREATE SCHEMA [XE] AUTHORIZATION [dbo]
GO

Create the View (using the XQuery from my previous post:

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/************************************************************
 * AUTHOR: Eric Zierdt          *
 * CREATED: 05/08/2012          *
 * USAGE: SELECT * FROM [XE].[vw_Log_Shrink]  *
 ************************************************************/
CREATE VIEW [XE].[vw_Log_Shrink]
AS
 
WITH Data
AS (
 SELECT CAST(target_data AS XML) AS TargetData
 FROM sys.dm_xe_session_targets dt
 JOIN  sys.dm_xe_sessions ds ON ds.address = dt.event_session_address
 JOIN  sys.server_event_sessions ss ON ds.Name = ss.Name
 WHERE dt.target_name = 'ring_buffer'
  AND ds.Name = 'XE_Log_Shrink'
)
SELECT  DATEADD(hour,-5,XEventData.XEvent.value('(@timestamp)[1]', 'datetime'))  AS event_timestamp
   ,XEventData.XEvent.value('@name', 'varchar(4000)')       AS event_name
   ,DB_NAME(XEventData.XEvent.value('(data/value)[2]', 'VARCHAR(100)'))  AS DatabaseName
   ,XEventData.XEvent.value('(action/value)[2]', 'VARCHAR(512)')    AS client_hostname
   ,XEventData.XEvent.value('(action/value)[3]', 'VARCHAR(512)')    AS nt_username
   ,XEventData.XEvent.value('(action/value)[6]', 'VARCHAR(512)')    AS username
   ,XEventData.XEvent.value('(action/value)[5]', 'VARCHAR(512)')    AS sql_text
   ,XEventData.XEvent.value('(action/value)[4]', 'VARCHAR(512)')    AS session_id
   ,XEventData.XEvent.value('(action/value)[1]', 'VARCHAR(512)')    AS client_app_name
FROM Data d
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData ( XEvent )

GO

Thats all you have to do, now you can query this like a DMV, just write:

SELECT *
FROM [XE].[vw_Log_Shrink]
WHERE [event_timestamp] > '5/8/2012'

Let me know your thoughts.