4

I'm looking for a easy or manageable way to read an XML file, specifically the XML output for SQL Server deadlocks. I have some servers that are not being monitored for various reasons.

I found the following code on the interwebz, but its output is nothing but XML.

CREATE TABLE #errorlog (
                        LogDate DATETIME 
                        , ProcessInfo VARCHAR(100)
                        , [Text] VARCHAR(MAX)
                        );

DECLARE @tag VARCHAR (MAX) , @path VARCHAR(MAX);

INSERT INTO #errorlog EXEC sp_readerrorlog;

SELECT @tag = text
FROM #errorlog 
WHERE [Text] LIKE 'Logging%MSSQL\Log%';

DROP TABLE #errorlog;

SET @path = SUBSTRING(@tag, 38, CHARINDEX('MSSQL\Log', @tag) - 29);

SELECT 
    CONVERT(xml, event_data).query('/event/data/value/child::*') AS DeadlockReport,
    CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]', 'datetime') 
    AS Execution_Time
FROM sys.fn_xe_file_target_read_file(@path + '\system_health*.xel', NULL, NULL, NULL)
WHERE OBJECT_NAME like 'xml_deadlock_report';

Is there a way to take the raw XML output from this T-SQL code and display it as a deadlock graph or more easily readable text?

  • 1
    Try saving the XML as a file with an extension of xdl and then open in SSMS – Scott Hodgin Oct 16 '17 at 23:20
  • 1
    To get it into tabular form you can use blog.waynesheffield.com/wayne/code-library/deadlocks/… – Martin Smith Oct 17 '17 at 6:02
  • @ScottHodgin That worked! It kills me to know that I've been in IT as long as I have and didn't think about file extension trick :| I should've known better, thank you! – Sean Perkins Oct 17 '17 at 13:44
  • 2
    Wanna receive *.xdl files in your Inbox in near real-time? It's fairly simple with event notifications – Dave Mason Oct 17 '17 at 16:39
  • @ScottHodgin this works: changing the xml extension to xdl. . But also, delete the outer tags event, data, type value. the outer tag should be <deadlock>. – Phillip Ngan Nov 19 '19 at 1:46
6

You can bcp out and save it as .xdl. Many ways you can do it .. e.g. BCP OUT (I am using xp_cmdshell but you can use powershell or any other method).

Borrowing code from my answer (You need to adjust the code as per your requirement) :

-- bcp out as .xdl file. This is the deadlock graph that will be emailed. Note that it will be overwritten everytime !!

exec master..xp_cmdshell ''BCP.exe "SELECT  [Deadlockgraph].query(''''/TextData/deadlock-list'''') FROM dba_db.dbo.Deadlock where Notified = 0" queryout "d:\logs\deadlock.xdl" -c -q -T -Slocalhost''; ---- change localhost ..with the servername\instance or servername !!
| improve this answer | |
  • Thank you, Kin. I can see how this method could be very useful! – Sean Perkins Oct 17 '17 at 13:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.