set rowcount 100
select Deadlockgraph.value('(/TextData/deadlock-list/deadlock/@victim)[1]','sysname')[Blocked Process HostName],
X.x.value('(../@waitresource)','sysname')[waitresource],
X.x.value('(../@status)','sysname')[status],
X.x.value('(../@spid)','sysname')[spid], 
X.x.value('(../@clientapp)','sysname')[clientapp],
X.x.value('(../@lastbatchstarted)','sysname')[lastbatchstarted],
X.x.value('(../@lastbatchcompleted)','sysname')[lastbatchcompleted],
X.x.value('(../@loginname)','sysname')[loginname],
X.x.value('(../@isolationlevel)','sysname')[isolationlevel],
X.x.value('(../@lockTimeout)','sysname')[locktimeout],
X.x.value('(../@clientoption1)','sysname')[clientOption1],
X.x.value('(../@clientoption2)','sysname')[clientOption2],
X.x.value('(frame/@procname)[1]','sysname')[procname],
X.x.query('data(frame)')[frame],
X.x.query('data(../inputbuf)')[inputnbuffer],
Deadlockgraph.value('count(/TextData/deadlock-list/deadlock/process-list/process)', 'int') as Count,
DeadlockGraph
from DeadlockEvents
CROSS APPLY Deadlockgraph.nodes('/TextData/deadlock-list/deadlock/process-list/process/executionStack') AS X(x) 
order by  6 desc