SQL Server 2008 R2从ring buffer解析死锁XML问题浅析

在SQL Server中，如果没有配置扩展事件（Extended Events）捕获死锁的话，我们一般从ring buffer中获取死锁信息，一般我用下面脚本解析获取死锁的信息

SELECT 
    DeadlockGraph
,   [DB_ID] = DeadlockGraph.value ( '(/deadlock/resource-list//@dbid)[1]', 'int' )
,   [DB_NAME] = DB_NAME ( DeadlockGraph.value ( '(/deadlock/resource-list//@dbid)[1]', 'int' ) ) 
,   [LastTranStarted] = DeadlockGraph.value ( '(/deadlock/process-list/process/@lasttranstarted)[1]', 'datetime' )
FROM
(
   SELECT 
      CAST ( event_data.value ( '(event/data/value)[1]', 'varchar(max)' ) AS XML ) AS DeadlockGraph
   FROM    
   ( 
      SELECT    
         XEvent.query('.') AS event_data
      FROM      
         ( -- Cast the target_data to XML 
            SELECT    
               CAST( st.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'
               AND target_name = 'ring_buffer'
         ) AS Data  
      CROSS APPLY      -- Split out the Event Nodes
         TargetData.nodes ( 'RingBufferTarget/event[@name="xml_deadlock_report"]' ) AS XEventData ( XEvent )
   ) AS tab ( event_data )
) AS A
ORDER BY [LastTranStarted];

但是上面脚本，在Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64)等一些版本下报错，具体错误如下所示：

消息 9436，级别 16，状态 1，第 1 行 XML 分析: 行 53，字符 12，结束标记与开始标记不匹配

Msg 9436, Level 16, State 1, Line 1 XML parsing: line 53, character 12, end tag does not match start tag

当然不是所有版本都会遇到这个错误，出现这个错误的原因，在于保存死锁的XML格式有一些变化，然后XML解析时遇到了问题，我研究了一下，改写了一下脚本，可以用下面脚本解决这个问题：

SELECT 
    DeadLockGraph
,   [DB_ID] = DeadlockGraph.value ( '(/deadlock-list/deadlock/resource-list//@dbid)[1]', 'int' )
,   [DB_NAME] = DB_NAME ( DeadlockGraph.value ( '(/deadlock-list/deadlock/resource-list//@dbid)[1]', 'int' ) ) 
,   [LastTranStarted] = DeadlockGraph.value ( '(/deadlock-list/deadlock/process-list/process/@lasttranstarted)[1]', 'datetime' )     
FROM 
(
SELECT    CAST(REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'),
                        '<victimProcess', '</victimProcess><victimProcess'),
                    '<victim-list>', '<deadlock><victim-list><victimProcess>'),
                '<process-list>', '</victim-list><process-list>'),
            '<victim-list/>', '<deadlock><victim-list>'),
        '<victimProcess>' + CHAR(10) + SPACE(2) + '</victimProcess>', '') AS XML) 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'
) AS T
ORDER BY [LastTranStarted];