Can a diagram be created from deadlock xml extracted from the ring buffer in SQL Server 2008?

In later versions of SQL_Server, we view the event file in the object browser to look at deadlock diagrams. This particular client has SQL 2008 (compatibility 2005) and the only copy of deadlocks have been extracted from the ring buffer using the below script:

SELECT
   xed.value('@timestamp', 'datetime2(3)') as CreationDate,
   xed.query('.') AS XEvent
FROM
(
   SELECT CAST([target_data] AS XML) AS TargetData
   FROM sys.dm_xe_session_targets AS st
   INNER JOIN sys.dm_xe_sessions AS s
      ON s.address = st.event_session_address
   WHERE s.name = N'system_health'
         AND st.target_name = N'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (xed)
ORDER BY CreationDate DESC

The format is not compatible with our xdl viewers. Is there any way to get a diagram from the returned XML below?:

```



<deadlock>
<victim-list>
<victimProcess id="process3cf048"/>
</victim-list>
<process-list>
<process id="process3cf048" taskpriority="0" logused="0" waitresource="PAGE: 11:1:45050737" waittime="3573" ownerId="19491756" transactionname="user_transaction" lasttranstarted="2018-07-04T01:14:58.003" XDES="0x31b45b400" lockMode="S" schedulerid="4" kpid="2280" status="suspended" spid="72" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-07-04T01:05:29.587" lastbatchcompleted="2018-07-04T01:05:29.587" clientapp="SQLAgent - TSQL JobStep (Job 0x3404686D1D7A2F478F69577F5EEBAE41 : Step 1)" hostname="ae-es" hostpid="5340" loginname="ae\rmsSYSTEM" isolationlevel="read committed (2)" xactid="19491756" currentdb="11" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname="" line="2177" stmtstart="149730" stmtend="150314" sqlhandle="0x03000b00389b050021b69200bca300000100000000000000">
</frame>
<frame procname="" line="92" stmtstart="8170" stmtend="9080" sqlhandle="0x03000b00dea9f7571cb80601e6a30000010000000

Yes. That's XML embedded in XML, so you need to extract and un-escape the deadlock XML with a query like:

select cast(@doc.value('(/event/data/value/.)[1]', 'nvarchar(max)') as xml) deadlock

Then open the XML results in SSMS and save the file with an .xdl extension. Then re-open it with SSMS and you'll see the deadlock graph.

StackExchange Database Administrators Q#211323, answer score: 6