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
//searches the whole XML document, and[1]keeps the first match only.- In the first query, the alias
AS ObjectNamefollows thePostTimeexpression, so it names that column, not the object. The second query's alias holds a "schema.login" value. Add explicit aliases before reusing these queries.
Reference: XQuery examples for SQL Server event data (SQL Junkies).