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
//recherche dans tout le document XML, et[1]ne retient que la première occurrence.- Dans la première requête, l'alias
AS ObjectNamesuit l'expressionPostTime: il désigne donc cette colonne et non l'objet. L'alias de la seconde requête contient une valeur « schéma.login ». Ajoutez des alias explicites avant de réutiliser ces requêtes.
Référence : Exemples XQuery pour les données d'événements SQL Server (SQL Junkies).