Getting deadlock information

get_deadlock_info.sql

SELECT CONVERT(xml, event_data).query('/event/data/value/child::*') as xml1,
       CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime') as Execution_Time
into #temp
FROM sys.fn_xe_file_target_read_file('system_health*.xel', null, null, null)
WHERE object_name like 'xml_deadlock_report'

select Row_Number() OVER(ORDER BY execution_time desc) AS RowNumber,*  into #temp2 from #temp


create table #tempdeadlock
(rownumber int,
 Code varchar(max),
 Code2 varchar(max),
 Code3 varchar(max),
 Code4 varchar(max),
  Code5 varchar(max),
   Code6 varchar(max),
   PagelockObject varchar(200),
   DeadlockObject varchar(200),
   [processid] varchar(200),
 [KeylockObject] varchar(200),
 [Index] varchar(200),
 [IndexLockMode] varchar(5),
 [Victim] char(1),
  VictimProcessID NVarChar(50),
 [Procedure]  varchar(200),
 [LockMode] char(1),
 [ClientApp]  varchar(100),
 [HostName]  varchar(20),
 [LoginName] varchar(20),
 [spid] varchar(10),
 [TransactionTime] datetime,
   [InputBuffer] varchar(1000)) 


declare @max int
declare @min int
declare @intFlag int=1
--declare @text varchar(max)
--declare @xmltext xml
declare @textall varchar(max)
select @max=max(rownumber),@min=min(rownumber) from #temp2
set @intflag=@min 
--set @max=1
WHILE (@intFlag <=@max)
BEGIN
    PRINT @intFlag
	--select @text='<deadlock-list>'+replace(cast(xml1 as varchar(max)),'''','''''')+'</deadlock-list>' from #temp2 where rownumber=@intflag
 --   set @xmltext=cast(@text as xml)
	set @textall=
	'
	declare @text varchar(max)
	declare @xmltext xml
	select @text=''<deadlock-list>''+replace(cast(xml1 as varchar(max)),'''''''','''''''''''')+''</deadlock-list>'' from #temp2 where rownumber='+cast(@intflag as varchar(10))+'
	 set @xmltext=cast(@text as xml)
	insert into #tempdeadlock
	select '+ cast(@intflag as varchar(10))+ ' as rownumber, 
 [Code] =   replace(replace(replace(replace(Deadlock.Process.value(''executionStack[1]/frame[1]'', ''varchar(1000)''),CHAR(13)+CHAR(10),''''),char(13),''''),char(10),''''),'' '','' '') ,
[Code2] =   replace(replace(replace(replace(Deadlock.Process.value(''executionStack[1]/frame[2]'', ''varchar(1000)''),CHAR(13)+CHAR(10),''''),char(13),''''),char(10),''''),'' '','' '') ,
[Code3] =   replace(replace(replace(replace(Deadlock.Process.value(''executionStack[1]/frame[3]'', ''varchar(1000)''),CHAR(13)+CHAR(10),''''),char(13),''''),char(10),''''),'' '','' '') ,
[Code4] =   replace(replace(replace(replace(Deadlock.Process.value(''executionStack[1]/frame[4]'', ''varchar(1000)''),CHAR(13)+CHAR(10),''''),char(13),''''),char(10),''''),'' '','' '') ,
[Code5] =   replace(replace(replace(replace(Deadlock.Process.value(''executionStack[1]/frame[5]'', ''varchar(1000)''),CHAR(13)+CHAR(10),''''),char(13),''''),char(10),''''),'' '','' '') ,
[Code6] =   replace(replace(replace(replace(Deadlock.Process.value(''executionStack[1]/frame[6]'', ''varchar(1000)''),CHAR(13)+CHAR(10),''''),char(13),''''),char(10),''''),'' '','' '') ,
  [PagelockObject] = @xmltext.value(''/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname'', ''varchar(200)''),
 [DeadlockObject] [email protected](''/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname'', ''varchar(200)''),
[processid]=Deadlock.Process.value(''@id'', ''varchar(200)''),
 [KeylockObject] = Keylock.Process.value(''@objectname'', ''varchar(200)''),
 [Index] = Keylock.Process.value(''@indexname'', ''varchar(200)''),
 [IndexLockMode] = Keylock.Process.value(''@mode'', ''varchar(5)''),
 [Victim] = case when Deadlock.Process.value(''@id'', ''varchar(50)'') = DeadlockList.Graphs.value(''(victim-list/victimProcess[1]/@id)[1]'', ''NVarChar(50)'')  then 1 else 0 end,
 DeadlockList.Graphs.value(''(victim-list/victimProcess[1]/@id)[1]'', ''NVarChar(50)'') AS VictimProcessID,
 [Procedure] = Deadlock.Process.value(''executionStack[1]/frame[1]/@procname[1]'', ''varchar(200)''),
 [LockMode] = Deadlock.Process.value(''@lockMode'', ''char(1)''),
[ClientApp] = Deadlock.Process.value(''@clientapp'', ''varchar(100)''),
 [HostName] = Deadlock.Process.value(''@hostname'', ''varchar(20)''),
 [LoginName] = Deadlock.Process.value(''@loginname'', ''varchar(20)''),
 [spid] = Deadlock.Process.value(''@spid'', ''varchar(10)''),
 [TransactionTime] = Deadlock.Process.value(''@lasttranstarted'', ''datetime''),
  [InputBuffer] = replace(replace(replace(replace(Deadlock.Process.value(''inputbuf[1]'', ''varchar(1000)''),CHAR(13)+CHAR(10),''''),char(13),''''),char(10),''''),'' '','' '')
  from @xmltext.nodes(''/deadlock-list/deadlock/process-list/process'') as Deadlock(Process)
      LEFT JOIN @xmltext.nodes(''/deadlock-list/deadlock/resource-list/keylock'') as Keylock(Process)
        ON Keylock.Process.value(''owner-list[1]/owner[1]/@id'', ''varchar(50)'') = 
           Deadlock.Process.value(''@id'', ''varchar(50)'')
	  CROSS JOIN  @xmltext.nodes(''/deadlock-list/deadlock'') AS DeadlockList(Graphs)
	  '
  exec (@textall)

	SET @intFlag = @intFlag + 1
 
END

select distinct rownumber,'|' as '|',
 REPLACE(REPLACE(REPLACE(Code, CHAR(13) + CHAR(10), ''), CHAR(13), ''), CHAR(10), '') as Code ,'|' as '|', 
  REPLACE(REPLACE(REPLACE(Code2, CHAR(13) + CHAR(10), ''), CHAR(13), ''), CHAR(10), '') as Code2 ,'|' as '|', 
   REPLACE(REPLACE(REPLACE(Code3, CHAR(13) + CHAR(10), ''), CHAR(13), ''), CHAR(10), '') as Code3 ,'|' as '|', 
    REPLACE(REPLACE(REPLACE(Code4, CHAR(13) + CHAR(10), ''), CHAR(13), ''), CHAR(10), '') as Code4 ,'|' as '|', 
	 REPLACE(REPLACE(REPLACE(Code5, CHAR(13) + CHAR(10), ''), CHAR(13), ''), CHAR(10), '') as Code5 ,'|' as '|', 
	  REPLACE(REPLACE(REPLACE(Code6, CHAR(13) + CHAR(10), ''), CHAR(13), ''), CHAR(10), '') as Code6 ,'|' as '|', 

 PagelockObject ,'|' as '|',   DeadlockObject ,'|' as '|',   [processid] ,'|' as '|', [KeylockObject] ,'|' as '|',
 [Index] ,'|' as '|', [IndexLockMode] ,'|' as '|', [Victim] ,'|' as '|',  VictimProcessID ,'|' as '|', [Procedure]  ,'|' as '|', [LockMode] ,'|' as '|', 
 [ClientApp] ,'|' as '|', [HostName]  ,'|' as '|', [LoginName] ,'|' as '|',
 [spid] ,'|' as '|', [TransactionTime] ,'|' as '|',   [InputBuffer]  from 
#tempdeadlock where 1=1