read from extended events file

SELECT file_name, CAST(event_data AS XML) AS ‘event_data_XML’
FROM sys.fn_xe_file_target_read_file(‘c:folderxevents*.xel’, NULL, NULL, NULL)

–parsing xml

http://blogs.msdn.com/b/extended_events/archive/2010/03/09/reading-event-data-101-what-s-up-with-the-xml.aspx

SELECT
event_xml.value(‘(action[@name=”username”]/value)[1]’, ‘varchar(100)’) AS username,
event_xml.value(‘(action[@name=”database_name”]/value)[1]’, ‘varchar(100)’) AS dbname,
event_xml.value(‘(data[@name=”statement”]/value)[1]’, ‘varchar(max)’) AS sqlstatement,
event_xml.value(‘(action[@name=”collect_system_time”]/value)[1]’, ‘datetime’) AS date
FROM    (SELECT CAST(event_data AS XML) xml_event_data
FROM sys.fn_xe_file_target_read_file(‘D:Compliancecompliance*.xel’, ‘D:Compliancecompliance*.xem’, NULL, NULL)) AS event_table
CROSS APPLY xml_event_data.nodes(‘//event’) n (event_xml)

 

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.