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

Question

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?:

<event name="xml_deadlock_report" package="sqlserver" id="123" version="1" timestamp="2018-07-04T00:15:14.667Z">
  <data name="xml_report">
    <type name="unicode_string" package="package0" />
    <value>&lt;deadlock&gt;
 &lt;victim-list&gt;
  &lt;victimProcess id="process3cf048"/&gt;
 &lt;/victim-list&gt;
 &lt;process-list&gt;
  &lt;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"&gt;
   &lt;executionStack&gt;
    &lt;frame procname="" line="2177" stmtstart="149730" stmtend="150314" sqlhandle="0x03000b00389b050021b69200bca300000100000000000000"&gt;
    &lt;/frame&gt;
    &lt;frame procname="" line="92" stmtstart="8170" stmtend="9080" sqlhandle="0x03000b00dea9f7571cb80601e6a300000100000000000000"&gt;
    &lt;/frame&gt;
    &lt;frame procname="" line="53" stmtstart="4514" stmtend="5792" sqlhandle="0x03000b00333d1b553bc7b200d3a300000100000000000000"&gt;
    &lt;/frame&gt;
    &lt;frame procname="" line="2" stmtstart="6" sqlhandle="0x01000b00b5d2e138601f5208040000000000000000000000"&gt;
    &lt;/frame&gt;
   &lt;/executionStack&gt;
   &lt;inputbuf&gt;

exec EBB_PSF_ImportSales &amp;apos;G:\progra~1\es\winrms\import&amp;apos;   &lt;/inputbuf&gt;
  &lt;/process&gt;
  &lt;process id="process4483b88" taskpriority="0" logused="6076" waitresource="PAGE: 11:1:132712" waittime="11747" ownerId="19493225" transactionname="user_transaction" lasttranstarted="2018-07-04T01:15:03.157" XDES="0x1344f8cc0" lockMode="S" schedulerid="6" kpid="5948" status="suspended" spid="64" sbid="0" ecid="0" priority="0" trancount="3" lastbatchstarted="2018-07-04T01:15:03.157" lastbatchcompleted="2018-07-04T01:15:03.153" lastattention="2018-07-03T23:45:37.360" clientapp="ECommerce WebService" hostname="ae-es" hostpid="3472" loginname="rmsSYSTEM" isolationlevel="read committed (2)" xactid="19493225" currentdb="11" lockTimeout="4294967295" clientoption1="673187936" clientoption2="128056"&gt;
   &lt;executionStack&gt;
    &lt;frame procname="" line="359" stmtstart="36280" stmtend="39240" sqlhandle="0x03000b00bdc1b416537f2601dfa700000100000000000000"&gt;
    &lt;/frame&gt;
    &lt;frame procname="" line="79" stmtstart="4356" stmtend="4452" sqlhandle="0x03000b00f6e5a817d68c2601dfa700000100000000000000"&gt;
    &lt;/frame&gt;
    &lt;frame procname="" line="1" stmtstart="98" sqlhandle="0x01000b00c20b1a2dd036be87000000000000000000000000"&gt;
    &lt;/frame&gt;
    &lt;frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"&gt;
    &lt;/frame&gt;
   &lt;/executionStack&gt;
   &lt;inputbuf&gt;

(@p0 xml,@p1 xml output,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[EBB_PSFXML_Process] @xml = @p0, @xml_out = @p1 OUTPUT   &lt;/inputbuf&gt;
  &lt;/process&gt;
 &lt;/process-list&gt;
 &lt;resource-list&gt;
  &lt;pagelock fileid="1" pageid="45050737" dbid="11" objectname="" id="lockdb734b80" mode="IX" associatedObjectId="72057595923464192"&gt;
   &lt;owner-list&gt;
    &lt;owner id="process4483b88" mode="IX"/&gt;
   &lt;/owner-list&gt;
   &lt;waiter-list&gt;
    &lt;waiter id="process3cf048" mode="S" requestType="wait"/&gt;
   &lt;/waiter-list&gt;
  &lt;/pagelock&gt;
  &lt;pagelock fileid="1" pageid="132712" dbid="11" objectname="" id="lock80131400" mode="IX" associatedObjectId="72057594217758720"&gt;
   &lt;owner-list&gt;
    &lt;owner id="process3cf048" mode="IX"/&gt;
   &lt;/owner-list&gt;
   &lt;waiter-list&gt;
    &lt;waiter id="process4483b88" mode="S" requestType="wait"/&gt;
   &lt;/waiter-list&gt;
  &lt;/pagelock&gt;
 &lt;/resource-list&gt;
&lt;/deadlock&gt;
</value>
    <text />
  </data>
</event>

Answer 1

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.

Answer 2

Thanks to David Browne's answer, the following statement returns the correctly formatted XML:

SELECT
   xed.value('@timestamp', 'datetime2(3)') as CreationDate,
   cast(XEventData.xed.value('(data/value)[1]', 'nvarchar(max)') as xml) as DeadlockGraph2  
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