use tempdb
go
if exists (select * from sys.objects where name = 'sqlws_xev_xml_deadlock_report')
    drop view sqlws_xev_xml_deadlock_report
go
create view sqlws_xev_xml_deadlock_report as
with xevents (event_data)
as
(
    select event.query('.') as event_data from 
        ((select cast (xest.target_data as xml) as target_data
                from sys.dm_xe_sessions as xes
                inner join sys.dm_xe_session_targets as xest on (xes.address = xest.event_session_address)
                where xes.name = 'sqlws_xevents_xml_deadlock_report' and xest.target_name = 'ring_buffer') as td
            cross apply target_data.nodes ('//event[@name="xml_deadlock_report"]') as x (event))
)
select event_data.value ('(event/@name)[1]', 'varchar(max)') as event_name,
    event_data.value ('(event/@timestamp)[1]', 'datetime') as event_timestamp,
    event_data.query ('(event/data[@name="xml_report"]/value)[1]/node()') as [xml_report],
    event_data.value ('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(max)') as action_client_app_name,
    event_data.value ('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') as action_client_hostname,
    event_data.value ('(event/action[@name="database_name"]/value)[1]', 'nvarchar(max)') as action_database_name,
    event_data.value ('(event/action[@name="nt_username"]/value)[1]', 'nvarchar(max)') as action_nt_username,
    event_data.value ('(event/action[@name="session_id"]/value)[1]', 'int') as action_session_id,
    event_data.value ('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') as action_sql_text
    from xevents
go

--SQL Server Extended Events script provided by Ramesh Meyyappan (mailto:rmeyyappan@sqlworkshops.com), SQL Consulting GmbH, Munich, Germany, http://www.sqlworkshops.com. Copyright: 2011-2016 Ramesh Meyyappan.
--Please email your comments, feedback and suggestions to Ramesh @ mailto:rmeyyappan@sqlworkshops.com, keep yourself up to date by subscribing to our newsletter at https://newsletter.sqlworkshops.com. Connect with me in LinkedIn: http://de.linkedin.com/in/rmeyyappan, on twitter https://twitter.com/SQLWorkshops.
--This SQL Server Extended Events script is provided to you under "Free Community License" model, Please read the EULA for additional information @ www.sqlvideo.com/eula. You acknowledge upon downloading or using the SQL Server Extended Events script that you have reviewed and agreed to all of the terms and conditions set forth in the EULA (http://www.sqlvideo.com/eula).
--About Us: We are a consulting company, SQL Consulting GmbH, based in Munich, Germany. We provide onsite and offsite SQL Server Performance Tuning and Troubleshooting Consulting. Please contact us at mailto:support@sqlworkshops.com or visit http://www.sqlworkshops.com if you are interested in any of our services.
--Download SQLTest, our SQL Server Performance, Load, Stress and Unit Test Tool from http://www.sqltest.org.
--Download sp_whopro(TM) script, our SQL Server Activity Monitoring and Logging Stored Procedure from http://www.sqldownload.com.
--Checkout our SQL Server Videos: http://www.sqlvideo.com

if exists (select * from sys.server_event_sessions where name = 'sqlws_xevents_xml_deadlock_report')
    drop event session sqlws_xevents_xml_deadlock_report on server
go
create event session sqlws_xevents_xml_deadlock_report on server
    add event sqlserver.xml_deadlock_report (action (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.nt_username, sqlserver.session_id, sqlserver.sql_text) where counter <= 100)
    add target package0.ring_buffer 
    with (event_retention_mode = ALLOW_SINGLE_EVENT_LOSS, memory_partition_mode = NONE)
go

/*
alter event session sqlws_xevents_xml_deadlock_report on server state = start
go

--Execute your workload

with xevents (event_name)
as
(
    select event.value ('(@name)[1]', 'varchar(max)') as event_name
        from ((select cast (xest.target_data as xml) as target_data
                from sys.dm_xe_sessions as xes
                inner join sys.dm_xe_session_targets as xest on (xes.address = xest.event_session_address)
                where xes.name = 'sqlws_xevents_xml_deadlock_report' and xest.target_name = 'ring_buffer') as td
            cross apply target_data.nodes ('//event[@name="xml_deadlock_report"]') as x (event))
)
select event_name, count(*) from xevents
    group by event_name
go

select * from sqlws_xev_xml_deadlock_report
go

alter event session sqlws_xevents_xml_deadlock_report on server state = stop
go
*/