LLI Wiki — My Digital Knowledge Base

Interroger des données d'événements XML avec XQuery dans SQL Server

Utiliser les expressions XQuery value() en T-SQL pour filtrer et extraire des champs de données d'événements XML stockées dans une table.

SQL Server expose le XML par le type de données xml. La méthode value() évalue un chemin XQuery et renvoie une valeur scalaire convertie dans le type SQL demandé. Ce motif permet de filtrer et d'extraire des champs de données d'événements XML, par exemple les lignes d'une table de journal qui conserve la sortie EVENTDATA() des déclencheurs DDL. Les exemples supposent une table dbo.EventLog avec une colonne xml nommée EventInstance.

Filtrer les événements sur un objet et sur un texte de la commande

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%'

Lister les couples schéma et login distincts du journal

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

Remarques

Référence : Exemples XQuery pour les données d'événements SQL Server (SQL Junkies).

Mastodon LLIMastodon SocialFosstodon