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)