WITH TX AS ( SELECT StartTime, CAST(TextData AS xml) AS TextData FROM sys.fn_trace_gettable('C:\! FB\Rhenus\2018-02-19\20180219_14H30_DEADLOCK_.trc', DEFAULT) WHERE EventClass = 148 ), TVM AS ( SELECT StartTime, v.value('(./inputbuf)[1]','nvarchar(max)') AS Query, i.value('(./deadlock/@victim)[1]','varchar(32)') AS ProcessVictim, v.value('(./@id)[1]','varchar(32)') AS ProcessID FROM TX CROSS APPLY TextData.nodes('/deadlock-list') AS X(i) CROSS APPLY TextData.nodes('/deadlock-list/deadlock/process-list/process') AS V(v) ), TVV AS ( SELECT DENSE_RANK() OVER (ORDER BY StartTime) AS ID, StartTime, Query, CASE WHEN ProcessVictim = ProcessID THEN 'Victim!' ELSE 'Alive' END AS FinalState FROM TVM ), TQV AS ( SELECT ID, Query FROM TVV WHERE FinalState = 'Victim!' ) SELECT TVV.ID, TVV.StartTime, TVV.Query, CASE WHEN TQV.ID IS NOT NULL THEN 'Victim!' ELSE 'Alive' END AS FinalState FROM TVV LEFT OUTER JOIN TQV ON TVV.ID = TQV.ID AND TVV.Query = TQV.Query ORDER BY 1;