I was running this query in SQL Server 2008 (10.0.5890), Collation: SQL_Latin1_General_CP1_CI_AS
SELECT
CAST(event_data.value('(event/data/value)[1]',
'NVARCHAR(max)') AS XML) AS DeadlockGraph
FROM
( SELECT
XEvent.query('.') AS event_data
FROM
( -- Cast the target_data to XML 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'
AND target_name = 'ring_buffer' ) AS Data -- Split out the Event Nodes CROSS APPLY TargetData.nodes('RingBufferTarget/ event[@name="xml_deadlock_report"]') AS XEventData ( XEvent ) ) AS tab ( event_data )
But it was always ended with the following error:
Msg 9420, Level 16, State 1, Line 1
XML parsing: line 22203, character 102, illegal xml character
Even if I changed it to VARCHAR(max) it remains the same. Though this same script was running fine and without any issue in another server with the same version & collation.
Sample XML result from the working database: Proc [Database Id = 5 Object Id = 2033194789]
</process>
<process id="process55d288" taskpriority="0" logused="584" waitresource="KEY: 5:72057594332250112 (6b0281c999d7)" waittime="1281" ownerId="1679" transactionname="EventGetQueueTopSp" lasttranstarted="2016-07-02T20:08:56.350" XDES="0x8209f990" lockMode="U" schedulerid="4" kpid="2880" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-07-02T20:09:12.370" lastbatchcompleted="2016-07-02T20:08:59.090" lastattention="1900-01-01T00:00:00.090" clientapp="EventService Queue Check" hostname="WVERPDB" hostpid="3148" loginname="sa" isolationlevel="read uncommitted (1)" xactid="1679" currentdb="5" lockTimeout="4294967295" clientoption1="671221856" clientoption2="128056">
<executionStack>
<frame procname="" line="96" stmtstart="8066" stmtend="8714" sqlhandle="0x0300050009a8007d9eadc300449e00000100000000000000" />
</executionStack>
<inputbuf>
</deadlock>
Here is the SQL version for both DB
Microsoft SQL Server 2008 (SP3) - 10.0.5890.0 (X64) Apr 2 2015 16:07:19 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)