Its nice to be able to query for deadlocks directly within the db without needing to use SSMS.  However I'm currently using this disaster to locate recent deadlock's by reading the system_health event ring_buffer. 

    select top 2000000000
          XEvent.value('@timestamp', 'datetime2(3)') as CreationDateUtc,
          --
          -- Extract the <deadlock>...</deadlock> tag from within the event
          -- Todo: Surely there is a better (xml) way to do this.
          --
          substring(convert(varchar(max), XEvent.query('.')), 
              -- start
              patindex('%<deadlock%', convert(varchar(max), XEvent.query('.'))),          
              -- end
              patindex('%</deadlock%', convert(varchar(max), XEvent.query('.'))) -
                  patindex('%<deadlock%', convert(varchar(max), XEvent.query('.'))) + 11 -- 11 to include for '</deadlock>'
              ) AS XdlFile
     from 
        (
          select cast (target_data as xml) as TargetData
            from sys.dm_xe_session_targets st with (nolock)
            join sys.dm_xe_sessions s with (nolock)
              on s.address = st.event_session_address
           where [name] = 'system_health'
             and st.target_name = N'ring_buffer'
        ) as Data
     cross apply TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)
     order by CreationDateUtc desc

It works fine however the events don't seem hang around very long (like 24 hours?)  I guess this is the "ring buffer" part of it.  Now I stumbled onto a link that was reading the system_health "file" which has similar information:

    select event_data = CONVERT(XML, event_data) 
    from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
    where event_data like '%xml_deadlock%'  

Is this file the same as the ring buffer but just stays around longer?  Any disadvantages to using a file?  Does anyone with some XML skills want to convert the top script?

The goal is to copy/paste the XdlFile field into a new file and read it directly into SSMS or Sql Sentry Plan Explorer using "File Open".

Ring Buffer Links:

http://www.sqlskills.com/blogs/jonathan/multi-victim-deadlocks/
        https://www.sqlskills.com/blogs/jonathan/graphically-viewing-extended-events-deadlock-graphs/
        http://www.mssqltips.com/sqlservertip/1234/capturing-sql-server-deadlock-information-in-xml-format/
        http://blogs.msdn.com/b/sqldatabasetalk/archive/2013/05/01/tracking-down-deadlocks-in-sql-database.aspx
        http://dba.stackexchange.com/questions/10644/deadlock-error-isnt-returning-the-deadlock-sql/10646#10646        

File:    
        https://www.mssqltips.com/sqlservertip/3636/query-data-from-extended-events-in-sql-server/


@@version = Microsoft SQL Server 2012 (SP3-CU5) (KB3180915) - 11.0.6544.0 (X64)