• Looking at the XQuery code a year later, I do see that there are some things that are not necessarily written well. Try this one out:

    select CAST(

    REPLACE(

    REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'),

    '<victim-list>', '<deadlock><victim-list>'),

    '<process-list>','</victim-list><process-list>')

    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[@name="xml_deadlock_report"]') AS XEventData (XEvent)

    If that doesn't quite get it, try pulling the target_data into a xml variable and then working off of that:

    declare @xml xml

    select @xml = target_data

    from sys.dm_xe_session_targets

    join sys.dm_xe_sessions on event_session_address = address

    where name = 'system_health'

    select CAST(

    REPLACE(

    REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'),

    '<victim-list>', '<deadlock><victim-list>'),

    '<process-list>','</victim-list><process-list>')

    as xml) as DeadlockGraph

    FROM

    (select @xml as TargetData) AS Data

    CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]