I have a small xEvent that is sending the results as .xel to an event_file in G:\XEvents and I want to explore the data with SSMS. I have been looking for a solution and found several posts about reading the XML created by ring buffers. But I can't get it to work on my event_file.
I read Jes Schultz Borland's post How to Query Extended Events Target XML but It is not working for me. I have not done anything significant with XML, and when I open the event_file with word, it does not look like XML.
I can display it by using SSMS; > Instance > Management > Extended Events > Sessions > [MyEvent] > Package > View Target data, but I can't really query it.
Best Answer
You are working under a false assumption, event_files do not store the data as XML, that is what ring_buffer does.
There are a few options but for me the simplest is to put the data in a SQL database.
Once you have the data displayed in SSMS: Instance > Management > Extended Events > Sessions > [MyEvent] > Package > View Target data
Use the new toolbar menu item 'extended events' and export to a table in SQL database.
The database needs to exist when you export but the table does not. I used a database named xEvent on a test/sandbox instance.
From the Toolbar: Extended Events > Export to > Table.. > (Connect to 'test/sandbox instance' > Existing database > (Type in any name you like)
Use standard SQL to query.
Reference Advanced Viewing of Target Data from Extended Events in SQL Serve