Sql Server : Query XML file and filter elements based on attributes

Quick one today

Once in a while, we need to process XML files (a.k.a. deadlock XML files) to retrieve some pertinent information to uncover the deadlock patterns.

In the past, we’ve seen some XML DML queries to parse XML files.

• value function
• Another value function example
• Insert/Update
• Insert/ Update with empty node

Here we’ll focus on filtering capability based on attribute values in elements:

Take an deadlock XML for example:

The goal is to search for the action element with attribute value as “collect_system_time“.

--
-- Parse & Filter XMl file data
--
SELECT
	  ROW_NUMBER() OVER(ORDER BY DeadLockXML.value('(/event/@timestamp)[1]', 'DATETIME2'))											AS [RowN]
	, DeadLockXML.value('(/event/action[@name="collect_system_time"]/value)[1]', 'DATETIME') AS SystemTime
FROM  dbo.Deadlocks_07262018_Step2
GO

The [] allow us to provide the value to filter the XMl elements:

(/event/action[@name="collect_system_time"]/value)[1]'

 

Hope this helps,
_Sqltimes

 

Read Full Post »