Can you guys help me presenting this in a table like format?

Question

I would like to display the information captured on the query below in a table format, that will be used for a report, in SSRS:

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') AS XEventData (XEvent)
    WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

---

--- Best Regards, Igor Santos Twitter: @sqlsantos Blog: sqlsantos.wordpress.com

Answer 1

All deadlock graphs are different so this can be a bit tricky.  You can't always guarantee the graph will contain certain elements and there may be a number of participants involved.  Having said that, there are some common elements and attributes.

Have a look at this example where I extract clean up the XML and extract the transaction startdate and inputbuffer:

USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('tempdb..#tmp' ) IS NOT NULL DROP TABLE #tmp
GO
CREATE TABLE #tmp ( rowId INT IDENTITY PRIMARY KEY, badXML VARCHAR(MAX), goodXML XML )
GO

INSERT INTO #tmp ( badXML )
VALUES
( '<deadlock-list>  <victim-list>   <victimProcess id="process5258bc8"/>   <process-list>    <process id="process5258bc8" taskpriority="0" logused="0" waitresource="PAGE: 8:1:10027530" waittime="9695" ownerId="1055431021" transactionname="SELECT" lasttranstarted="2011-03-16T09:46:37.910" XDES="0x28fb7f760" lockMode="S" schedulerid="13" kpid="14184" status="suspended" spid="71" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2011-03-16T09:46:37.910" lastbatchcompleted="2011-03-16T09:46:37.907" clientapp=".Net SqlClient Data Provider" hostname="someHost301" hostpid="4300" loginname="SOMEDOMAIN\someHost304$" isolationlevel="read committed (2)" xactid="1055431021" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">     <executionStack>      <frame procname="" line="7" stmtstart="486" sqlhandle="0x0200000061dca31412f16447ff510dcefb5b290062a425a1">      </frame>      <frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">      </frame>     </executionStack>     <inputbuf> SELECT * FROM someTable </inputbuf>    </process>    <process id="process2b34922c8" taskpriority="0" logused="4168" waitresource="PAGE: 8:1:33495280" waittime="5006" ownerId="1055429440" transactionname="user_transaction" lasttranstarted="2011-03-16T09:46:37.110" XDES="0x5999603b0" lockMode="IX" schedulerid="16" kpid="7952" status="suspended" spid="171" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-03-16T09:46:37.127" lastbatchcompleted="2011-03-16T09:46:37.120" lastattention="2011-03-16T09:41:34.687" clientapp=".Net SqlClient Data Provider" hostname="someHost302" hostpid="3312" loginname="SOMEDOMAIN\someAccount" isolationlevel="read committed (2)" xactid="1055429440" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">     <executionStack>      <frame procname="" line="1" stmtstart="5650" sqlhandle="0x020000000f7a153607f13c82e354ef716d3c1a913ed7a544">      </frame>      <frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">      </frame>     </executionStack>     <inputbuf> SELECT * FROM someTable </inputbuf>    </process>   </process-list>   <resource-list>    <pagelock fileid="1" pageid="10027530" dbid="8" objectname="" id="lock5367aeb80" mode="IX" associatedObjectId="72057611960975360">     <owner-list>      <owner id="process2b34922c8" mode="IX"/>     </owner-list>     <waiter-list>      <waiter id="process5258bc8" mode="S" requestType="wait"/>     </waiter-list>    </pagelock>    <pagelock fileid="1" pageid="33495280" dbid="8" objectname="" id="lock58bc92580" mode="S" associatedObjectId="72057611961171968">     <owner-list>      <owner id="process5258bc8" mode="S"/>     </owner-list>     <waiter-list>      <waiter id="process2b34922c8" mode="IX" requestType="wait"/>     </waiter-list>    </pagelock>   </resource-list>  </deadlock> </deadlock-list> ' ),
( '<deadlock-list>  <victim-list>   <victimProcess id="process4ff1b88"/>   <process-list>    <process id="process4ff1b88" taskpriority="0" logused="0" waitresource="PAGE: 8:1:10039576" waittime="724" ownerId="1055766658" transactionname="SELECT" lasttranstarted="2011-03-16T09:47:53.240" XDES="0x375835ca0" lockMode="S" schedulerid="9" kpid="4796" status="suspended" spid="390" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2011-03-16T09:47:53.240" lastbatchcompleted="2011-03-16T09:47:53.240" lastattention="2011-03-16T09:41:42.427" clientapp=".Net SqlClient Data Provider" hostname="someHost303" hostpid="1196" loginname="SOMEDOMAIN\someAccount" isolationlevel="read committed (2)" xactid="1055766658" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">     <executionStack>      <frame procname="" line="1" stmtstart="196" sqlhandle="0x020000009e0bac02ad90c01c3253ff7edf94b57f9fe25319">      </frame>      <frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">      </frame>     </executionStack>     <inputbuf> SELECT * FROM someTable </inputbuf>    </process>    <process id="process5bbc4bb88" taskpriority="0" logused="4024" waitresource="PAGE: 8:1:10023394" waittime="722" ownerId="1055765593" transactionname="user_transaction" lasttranstarted="2011-03-16T09:47:52.920" XDES="0x6d6c15950" lockMode="IX" schedulerid="2" kpid="12044" status="suspended" spid="88" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-03-16T09:47:52.930" lastbatchcompleted="2011-03-16T09:47:52.927" clientapp=".Net SqlClient Data Provider" hostname="someHost301" hostpid="1076" loginname="SOMEDOMAIN\someAccount" isolationlevel="read committed (2)" xactid="1055765593" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">     <executionStack>      <frame procname="" line="1" stmtstart="5830" sqlhandle="0x02000000e33d7729ddcae28f501fa401c5c4a2c85a5893ac">      </frame>      <frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">      </frame>     </executionStack>     <inputbuf> SELECT * FROM someTable </inputbuf>    </process>   </process-list>   <resource-list>    <pagelock fileid="1" pageid="10039576" dbid="8" objectname="" id="lock6cf7afb00" mode="IX" associatedObjectId="72057611959664640">     <owner-list>      <owner id="process5bbc4bb88" mode="IX"/>     </owner-list>     <waiter-list>      <waiter id="process4ff1b88" mode="S" requestType="wait"/>     </waiter-list>    </pagelock>    <pagelock fileid="1" pageid="10023394" dbid="8" objectname="" id="lock52a041080" mode="S" associatedObjectId="72057611961040896">     <owner-list>      <owner id="process4ff1b88" mode="S"/>     </owner-list>     <waiter-list>      <waiter id="process5bbc4bb88" mode="IX" requestType="wait"/>     </waiter-list>    </pagelock>   </resource-list>  </deadlock> </deadlock-list> ' )
GO

UPDATE #tmp
set badXML = REPLACE( REPLACE( badXML, '<victim-list>', '<deadlock><victim-list>' ), '<process-list>','</victim-list><process-list>' )
GO


DECLARE @i INT = 0

WHILE 1=1
BEGIN

	SET @i += 1
	
	BEGIN TRY
	
		UPDATE top(1) #tmp
		set goodXML = badXML 
		WHERE goodXML IS NULL

		IF @@rowcount = 0 BREAK

	END TRY
	BEGIN CATCH
		
		IF @@error = 9436	--XML parsing: line %d, character %d, end tag does not match start tag
			UPDATE TOP(1) #tmp
			SET goodXML = '<error/>' 
			WHERE goodXML IS NULL
		
	END CATCH

	IF @i > 999 BEGIN RAISERROR( 'Too many loops!', 16, 1 )  BREAK END
	
END

SELECT @i loops
GO

SELECT *
FROM #tmp
GO


-- Lose errors here ...
SELECT
	t.rowId,
	t.goodXML,
	x.y.query('data(//inputbuf)') inputbuf,
	x.y.value('(//@lasttranstarted)[1]', 'VARCHAR(MAX)') lasttranstarted,
	x.y.value('(//@lockTimeout)[1]', 'VARCHAR(MAX)') lockTimeout
FROM #tmp t
	CROSS APPLY t.goodXML.nodes( 'deadlock-list/*' ) x(y)
GO