LLI Wiki — My Digital Knowledge Base

Querying XML event data with XQuery in SQL Server

Use XQuery value() expressions in T-SQL to filter and extract fields from XML event data stored in a table.

SQL Server exposes XML through the xml data type. The value() method evaluates an XQuery path and returns one scalar value converted to the SQL type you name. This pattern filters and extracts fields from XML event data, for example the rows of a log table that keeps the EVENTDATA() output of DDL triggers. The examples assume a table dbo.EventLog with an xml column named EventInstance.

Filter events on an object and on a text in the command

SELECT EventID,
EventInstance.value('(//EventType)[1]','nvarchar(30)') AS EventType
,EventInstance.value('concat((//SchemaName)[1],".", (//ObjectName)[1])', 'nvarchar(60)')
,EventInstance.value('concat((//SchemaName)[1],".", (//LoginName)[1])', 'nvarchar(60)')
,EventInstance.value('concat((//SchemaName)[1],".", (//TSQLCommand/CommandText)[1])', 'varchar(8000)')
,EventInstance.value('((//PostTime)[1]) ','DateTime')
AS ObjectName,*
FROM dbo.EventLog
where  EventInstance.value('concat((//SchemaName)[1],".", (//ObjectName)[1])', 'nvarchar(60)') ='SchemaA.TableA_old'
and EventInstance.value('concat((//SchemaName)[1],".", (//TSQLCommand/CommandText)[1])', 'varchar(8000)') like '%SearchText%'

List the distinct schema and login pairs in the log

SELECT 
EventInstance.value('concat((//SchemaName)[1],".", (//LoginName)[1])', 'nvarchar(60)')
AS ObjectName
into #tmp
FROM dbo.EventLog
select ObjectName from #tmp group by ObjectName

Notes

Reference: XQuery examples for SQL Server event data (SQL Junkies).

Mastodon LLIMastodon SocialFosstodon