How to read Deadlock from system health of SQL server

 How to read the Deadlock from the system health of the SQL server

This query can be used to analyze deadlock events from the system health extended events in SQL Server.

SELECT 
    xed.value('@timestamp', 'datetime') AS Creation_Date,
    xed.query('.') AS Extended_Event,
    xed.value('(//deadlock/process-list/process[1]/@clientapp)[1]', 'NVarChar(50)') AS VictimClientApp,
    xed.value('(//deadlock/process-list/process[1]/@spid)[1]', 'NVarChar(15)') AS VictimProcessID,
    CAST(REPLACE(xed.value('(//deadlock/process-list/process[1]/@lastbatchstarted)[1]', 'NChar(23)'), N'T', N' ') AS DATETIME) AS VictimLastBatchStarted,
    xed.value('(//deadlock/process-list/process[1]/@lockMode)[1]', 'NVarChar(15)') AS VictimLockMode,
    xed.value('(//deadlock/process-list/process[1]/@xactid)[1]', 'NVarChar(15)') AS VictimXActID,
    xed.value('(//deadlock/process-list/process[1]/@clientapp)[1]', 'NVarChar(50)') AS VictimClientApp,
    -- Live Process
    xed.value('(//deadlock/process-list/process[2]/@spid)[1]', 'NVarChar(15)') AS LiveProcessID,
    CAST(REPLACE(xed.value('(//deadlock/process-list/process[2]/@lastbatchstarted)[1]', 'NChar(23)'), N'T', N' ') AS DATETIME) AS LiveLastBatchStarted,
    xed.value('(//deadlock/process-list/process[2]/@lockMode)[1]', 'NVarChar(15)') AS LiveLockMode,
    xed.value('(//deadlock/process-list/process[2]/@xactid)[1]', 'NVarChar(15)') AS LiveXActID,
    xed.value('(//deadlock/process-list/process[2]/@clientapp)[1]', 'NVarChar(50)') AS LiveClientApp,
    -- Live Resource
    xed.value('(//deadlock/resource-list/pagelock[1]/@fileid)[1]', 'NVarChar(15)') AS LiveFileID,
    xed.value('(//deadlock/resource-list/pagelock[1]/@pageid)[1]', 'NVarChar(15)') AS LivePageID,
    xed.value('(//deadlock/resource-list/pagelock[1]/@objectname)[1]', 'NVarChar(50)') AS LiveObjectName,
    xed.value('(//deadlock/resource-list/pagelock[1]/@mode)[1]', 'NVarChar(50)') AS LiveLockModeHeld,
    xed.value('(//deadlock/resource-list/pagelock[1]/waiter-list/waiter/@mode)[1]', 'NVarChar(50)') AS VictimLockModeRequest,
    -- Victim Resource
    xed.value('(//deadlock/resource-list/pagelock[2]/@fileid)[1]', 'NVarChar(15)') AS VictimFileID,
    xed.value('(//deadlock/resource-list/pagelock[2]/@pageid)[1]', 'NVarChar(15)') AS VictimPageID,
    xed.value('(//deadlock/resource-list/pagelock[2]/@objectname)[1]', 'NVarChar(50)') AS VictimObjectName,
    xed.value('(//deadlock/resource-list/pagelock[2]/@mode)[1]', 'NVarChar(50)') AS VictimLockModeHeld,
    xed.value('(//deadlock/resource-list/pagelock[2]/waiter-list/waiter/@mode)[1]', 'NVarChar(50)') AS LiveLockModeRequest,
    -- Input Buffers
    xed.value('(//deadlock/process-list/process[1]/executionStack/frame/@procname)[1]', 'NVarChar(100)') AS VictimProcName,
    xed.value('(//deadlock/process-list/process[1]/executionStack/frame)[1]', 'VarChar(max)') AS VictimExecStack,
    xed.value('(//deadlock/process-list/process[2]/executionStack/frame/@procname)[1]', 'NVarChar(max)') AS LiveProcName,
    xed.value('(//deadlock/process-list/process[2]/executionStack/frame)[1]', 'VarChar(max)') AS LiveExecStack,
    RTRIM(LTRIM(REPLACE(xed.value('(//deadlock/process-list/process[1]/inputbuf)[1]', 'NVarChar(2048)'), NCHAR(10), N''))) AS VictimInputBuffer,
    RTRIM(LTRIM(REPLACE(xed.value('(//deadlock/process-list/process[2]/inputbuf)[1]', 'NVARCHAR(2048)'), NChar(10), N''))) AS LiveInputBuffer
FROM 
    (SELECT CAST([target_data] AS XML) AS Target_Data
     FROM sys.dm_xe_session_targets AS xt
     INNER JOIN sys.dm_xe_sessions AS xs ON xs.address = xt.event_session_address
     WHERE xs.name = N'system_health'
     AND xt.target_name = N'ring_buffer') AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed)
ORDER BY Creation_Date DESC;