SQL 2008
;WITH Deadlock as ( SELECT CAST(event_data.value('(event/data/value)[1]', 'varchar(max)') AS XML) AS DeadlockGraph FROM ( SELECT XEvent.query('.') AS event_data FROM ( -- Cast the target_data to XML SELECT CAST(target_data AS XML) AS TargetData FROM sys.dm_xe_session_targets st JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address WHERE name = 'system_health' AND target_name = 'ring_buffer' ) AS Data -- Split out the Event Nodes CROSS APPLY TargetData.nodes('RingBufferTarget/ event[@name="xml_deadlock_report"]') AS XEventData ( XEvent ) ) AS tab ( event_data ) ) select deadlockgraph.value('/deadlock[1]/process-list[1]/process[1]/@lasttranstarted','datetime') as lasttranstarted ,DeadlockGraph from deadlock
SQL 2012
;WITH Deadlock as ( SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph FROM ( SELECT XEvent.query('.') AS XEvent FROM ( SELECT CAST(target_data AS XML) AS TargetData FROM sys.dm_xe_session_targets st JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer' ) AS Data CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData ( XEvent ) ) AS src ) select deadlockgraph.value('/deadlock[1]/process-list[1]/process[1]/@lasttranstarted','datetime') as lasttranstarted ,DeadlockGraph from deadlock
see for more information about this topic url: https://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/