select CONVERT( xml, XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')) as DeadlockGraph
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 name = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

<deadlock>
  <victim-list>
    <victimProcess id="process6430748" />
  </victim-list>
  <process-list>
    <process id="process6430748" taskpriority="0" logused="0" waitresource="PAGE: 17:22:8689" waittime="2293" ownerId="2733945332" transactionname="INSERT" lasttranstarted="2012-09-18T09:37:34.633" XDES="0x29f702a900" lockMode="S" schedulerid="13" kpid="2916" status="suspended" spid="137" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-09-18T09:37:34.533" lastbatchcompleted="2012-09-18T09:37:34.533" clientapp="cogent.asset" hostname="-1:war-cog-tml-01v.asset.local" hostpid="2584" loginname="CogentUser" isolationlevel="read committed (2)" xactid="2733945332" currentdb="17" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128568">
      <executionStack>
        <frame procname="" line="245" stmtstart="20606" stmtend="23180" sqlhandle="0x030011008ad0af3e987a070149a000000100000000000000" />
      </executionStack>
      <inputbuf>
Proc [Database Id = 17 Object Id = 1051709578]   </inputbuf>
    </process>
    <process id="process7c33288" taskpriority="0" logused="27340" waitresource="PAGE: 17:22:8434" waittime="2623" ownerId="2733257730" transactionname="INSERT" lasttranstarted="2012-09-18T09:37:13.997" XDES="0x39e9f11950" lockMode="IX" schedulerid="11" kpid="12084" status="suspended" spid="69" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-09-18T09:37:13.890" lastbatchcompleted="2012-09-18T09:37:13.887" lastattention="2012-09-18T05:37:50.263" clientapp="cogent.asset" hostname="-1:WAR-COG-TML-03V.asset.local" hostpid="3740" loginname="CogentUser" isolationlevel="read committed (2)" xactid="2733257730" currentdb="17" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128568">
      <executionStack>
        <frame procname="" line="245" stmtstart="20606" stmtend="23180" sqlhandle="0x030011008ad0af3e987a070149a000000100000000000000" />
      </executionStack>
      <inputbuf>
Proc [Database Id = 17 Object Id = 1051709578]   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <pagelock fileid="22" pageid="8689" dbid="17" objectname="" id="lock3676adf200" mode="IX" associatedObjectId="72057607072907264">
      <owner-list>
        <owner id="process7c33288" mode="IX" />
      </owner-list>
      <waiter-list>
        <waiter id="process6430748" mode="S" requestType="wait" />
      </waiter-list>
    </pagelock>
    <pagelock fileid="22" pageid="8434" dbid="17" objectname="" id="lock36772e3700" mode="S" associatedObjectId="72057607072907264">
      <owner-list>
        <owner id="process6430748" mode="S" />
      </owner-list>
      <waiter-list>
        <waiter id="process7c33288" mode="IX" requestType="wait" />
      </waiter-list>
    </pagelock>
  </resource-list>
</deadlock>

select CONVERT( xml, XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')) as DeadlockGraph
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 name = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'
OPTION(MAXDOP 1)

;WITH log_deadlocks
AS (
	select CONVERT( xml, XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')) as DeadlockGraph
	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 name = 'system_health') AS Data
	CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
	where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'
	)
Select
	--DeadlockDateTime,
	DeadlockGraph,
	DbName = DB_NAME(ResList.KeyLock.value('@dbid', 'INT')),
    ObjectName = ResList.KeyLock.value('@objectname', 'sysname'),
    IndexName = ResList.KeyLock.value('@indexname', 'sysname'),
    ResultingLockMode = ResList.KeyLock.value('@mode', 'varchar(10)'),
    OwnerID = OwnList.Own.value('@id', 'sysname'),
    OwnerLockMode = OwnList.Own.value('@mode', 'varchar(10)'),
    WaiterID = WaitList.Wait.value('@id', 'sysname'),
    WaiterLockMode = WaitList.Wait.value('@mode', 'varchar(10)')
From Log_Deadlocks
-- Shred keylock node of the resource-list
Cross Apply DeadlockGraph.nodes('//resource-list/keylock') ResList(KeyLock)
-- Shred the owner node beneath the resource-list/keylock node
Cross Apply ResList.KeyLock.nodes('./owner-list/owner') OwnList(Own)
-- Shred the waiter node beneath the resource-list/keylock node
Cross Apply ResList.KeyLock.nodes('./waiter-list/waiter') WaitList(Wait);