5

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?

7
  • 2
    Try saving the XML as a file with an extension of xdl and then open in SSMS Oct 16, 2017 at 23:20
  • 2
    To get it into tabular form you can use blog.waynesheffield.com/wayne/code-library/deadlocks/… Oct 17, 2017 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!
    – AKDiscer
    Oct 17, 2017 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, 2017 at 16:39
  • 1
    @MartinSmith This is the only working solution I have found to get it into tabular format. Thank you very much for sharing.
    – Asher
    Oct 29, 2021 at 12:56

1 Answer 1

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 !!
1
  • Thank you, Kin. I can see how this method could be very useful!
    – AKDiscer
    Oct 17, 2017 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.