It seems an ever growing number of tools in the dba’s arsenal are built on XML. From Extended Events, to Event Notification even Enterprise Policy Management..

With that instead of re-inventing the wheel every time, I have to parse out the XML and use it. I thought a cheat sheet was needed!

To begin we need some XML, I’ll be using the results of Event Notification where I was monitoring logins and blocking. (I have removed a lot nodes to keep the XML small(er) readable.). Its loaded into a table with a column called FullLog that is XML.

The XML Looks like this:

<EVENT_INSTANCE>
<EventType>BLOCKED_PROCESS_REPORT</EventType>
<PostTime>2014-03-08T18:46:52.170</PostTime>
<SPID>4</SPID>
<TextData>
<blocked-process-report monitorLoop="1556">
<blocked-process>
<process clientapp="Microsoft SQL Server Management Studio - Query" hostname="NAVI-PC" isolationlevel="read committed (2)" >
<inputbuf>
SELECT FirstName , LastName , SUM(soh.TotalDue) AS TotalDue , MAX(OrderDate) AS LastOrder
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.Customer AS c ON soh.CustomerID = c.CustomerID
INNER JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID
WHERE soh.OrderDate &gt; = '2011/ 01/ 01'
GROUP BY c.CustomerID , FirstName , LastName
</inputbuf>
</process>
</blocked-process>
<blocking-process>
<process clientapp="Microsoft SQL Server Management Studio - Query" hostname="NAVI-PC" loginname="Navi-PC\Stephen" isolationlevel="read committed (2)" >
<executionStack />
<inputbuf>
DECLARE @SalesOrderHeaderID INT
BEGIN TRANSACTION
INSERT INTO Sales.SalesOrderHeader
( RevisionNumber , OrderDate , DueDate , ShipDate , Status , OnlineOrderFlag ,
PurchaseOrderNumber , AccountNumber , CustomerID , SalesPersonID ,
TerritoryID , BillToAddressID , ShipToAddressID , ShipMethodID , CreditCardID ,
CreditCardApprovalCode , CurrencyRateID , Comment , rowguid , ModifiedDate)
VALUES ( 5 , '2011/ 06/ 20' , '2011/ 06/ 25' , '2011/ 06/ 30' , 5 , 0 , NULL , '10-4030-018749' , 18749 , NULL , 6 , 28374 , 28374 , 1 , 8925 , '929849Vi46003' ,
NULL , NULL , NEWID() , GETD
</inputbuf>
</process>
</blocking-process>
</blocked-process-report>
</TextData>
<DatabaseID>7</DatabaseID>
<SessionLoginName />
</EVENT_INSTANCE>

XML Query Cheat Sheet

Query XML Elements

In this example we want retrieve : BLOCKED_PROCCESS_REPORT

<EventType>BLOCKED_PROCESS_REPORT</EventType>

Select Statement

SELECT
FullLog.query('(/EVENT_INSTANCE/EventType/text())') AS 'EventType'
FROM [DBA].[dbo].[EventNotification]

clip_image001

Query XML Attribute’s

If we want to retrieve “Microsoft SQL Server Management Studio – Query”

<process clientapp="Microsoft SQL Server Management Studio - Query" hostname="NAVI-PC" isolationlevel="read committed (2)">

Select Statement

SELECT FullLog.value('(/EVENT_INSTANCE/TextData/blocked-process-report/blocking-process[1]/process/@clientapp)[1]',
'nvarchar(50)') AS 'BlockingSession'
FROM [DBA].[dbo].[EventNotification]
WHERE EventType = 'BLOCKED_PROCESS_REPORT'

clip_image002

Advertisements