How to read extended events .xel file











up vote
0
down vote

favorite












I have a certain request to read/parse the .xel file which is extended events file , How can I do this effectively and efficiently . One of my colleague advised to use API to parse the file , However I believe there should be a way that this can be achieved through SQL code itself . Help is much appreciated .










share|improve this question







New contributor




Manjunath is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
























    up vote
    0
    down vote

    favorite












    I have a certain request to read/parse the .xel file which is extended events file , How can I do this effectively and efficiently . One of my colleague advised to use API to parse the file , However I believe there should be a way that this can be achieved through SQL code itself . Help is much appreciated .










    share|improve this question







    New contributor




    Manjunath is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.






















      up vote
      0
      down vote

      favorite









      up vote
      0
      down vote

      favorite











      I have a certain request to read/parse the .xel file which is extended events file , How can I do this effectively and efficiently . One of my colleague advised to use API to parse the file , However I believe there should be a way that this can be achieved through SQL code itself . Help is much appreciated .










      share|improve this question







      New contributor




      Manjunath is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.











      I have a certain request to read/parse the .xel file which is extended events file , How can I do this effectively and efficiently . One of my colleague advised to use API to parse the file , However I believe there should be a way that this can be achieved through SQL code itself . Help is much appreciated .







      sql-server






      share|improve this question







      New contributor




      Manjunath is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.











      share|improve this question







      New contributor




      Manjunath is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      share|improve this question




      share|improve this question






      New contributor




      Manjunath is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      asked Nov 5 at 3:37









      Manjunath

      32




      32




      New contributor




      Manjunath is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.





      New contributor





      Manjunath is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






      Manjunath is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.
























          1 Answer
          1






          active

          oldest

          votes

















          up vote
          0
          down vote



          accepted










          To read .xel files, you can use sys.fn_xe_file_target_read_file function. For example:



          select cast(event_data as XML) as event_data
          from sys.fn_xe_file_target_read_file('D:FolderMySession*.xel', null, null, null)


          Further you may want to parse the returned XML to get the data in table format. To do this, you need to decide what data to extract from the XML and write the appropriate XPath expressions. For example:



          -- You have to know element names and their data types
          select
          n.value('(@name)[1]', 'varchar(50)') as event_name,
          n.value('(@package)[1]', 'varchar(50)') AS package_name,
          n.value('(@timestamp)[1]', 'datetime2') AS [utc_timestamp],
          n.value('(data[@name="duration"]/value)[1]', 'int') as duration,
          n.value('(data[@name="cpu_time"]/value)[1]', 'int') as cpu,
          n.value('(data[@name="physical_reads"]/value)[1]', 'int') as physical_reads,
          n.value('(data[@name="logical_reads"]/value)[1]', 'int') as logical_reads,
          n.value('(data[@name="writes"]/value)[1]', 'int') as writes,
          n.value('(data[@name="row_count"]/value)[1]', 'int') as row_count,
          n.value('(data[@name="last_row_count"]/value)[1]', 'int') as last_row_count,
          n.value('(data[@name="line_number"]/value)[1]', 'int') as line_number,
          n.value('(data[@name="offset"]/value)[1]', 'int') as offset,
          n.value('(data[@name="offset_end"]/value)[1]', 'int') as offset_end,
          n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') as statement,
          n.value('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') as database_name
          from (select cast(event_data as XML) as event_data
          from sys.fn_xe_file_target_read_file('D:FolderMySession*.xel', null, null, null)) ed
          cross apply ed.event_data.nodes('event') as q(n)





          share|improve this answer





















          • This sufficed the need . And are these the only fields we have for extended events ? If I need the query plan to be included , How can I do that ?
            – Manjunath
            Nov 5 at 17:09










          • No, these fields are just an example. You should read the data, that is captured from your session. First look at the xml read from the file, then construct appropriate XPath queries to transform it to table (if you need this). There are thousands of things that you can capture with XE, but this isn't related to the "how to read .xel file" question. You can only read what is written in the file, obviously.
            – Andrey Nikolov
            Nov 5 at 17:53










          • I did some investigation on this and now I am able to modify the XPath queries to get what I need for a given specific scenario / requirement . Your comments and answers helped a lot to dive in . This was really helpful
            – Manjunath
            Nov 6 at 5:58












          • I'm glad I was helpful! It will be very appreciated if you accept and upvote my answer.
            – Andrey Nikolov
            Nov 6 at 6:54











          Your Answer






          StackExchange.ifUsing("editor", function () {
          StackExchange.using("externalEditor", function () {
          StackExchange.using("snippets", function () {
          StackExchange.snippets.init();
          });
          });
          }, "code-snippets");

          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "1"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          convertImagesToLinks: true,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: 10,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });






          Manjunath is a new contributor. Be nice, and check out our Code of Conduct.










           

          draft saved


          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53147992%2fhow-to-read-extended-events-xel-file%23new-answer', 'question_page');
          }
          );

          Post as a guest
































          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes








          up vote
          0
          down vote



          accepted










          To read .xel files, you can use sys.fn_xe_file_target_read_file function. For example:



          select cast(event_data as XML) as event_data
          from sys.fn_xe_file_target_read_file('D:FolderMySession*.xel', null, null, null)


          Further you may want to parse the returned XML to get the data in table format. To do this, you need to decide what data to extract from the XML and write the appropriate XPath expressions. For example:



          -- You have to know element names and their data types
          select
          n.value('(@name)[1]', 'varchar(50)') as event_name,
          n.value('(@package)[1]', 'varchar(50)') AS package_name,
          n.value('(@timestamp)[1]', 'datetime2') AS [utc_timestamp],
          n.value('(data[@name="duration"]/value)[1]', 'int') as duration,
          n.value('(data[@name="cpu_time"]/value)[1]', 'int') as cpu,
          n.value('(data[@name="physical_reads"]/value)[1]', 'int') as physical_reads,
          n.value('(data[@name="logical_reads"]/value)[1]', 'int') as logical_reads,
          n.value('(data[@name="writes"]/value)[1]', 'int') as writes,
          n.value('(data[@name="row_count"]/value)[1]', 'int') as row_count,
          n.value('(data[@name="last_row_count"]/value)[1]', 'int') as last_row_count,
          n.value('(data[@name="line_number"]/value)[1]', 'int') as line_number,
          n.value('(data[@name="offset"]/value)[1]', 'int') as offset,
          n.value('(data[@name="offset_end"]/value)[1]', 'int') as offset_end,
          n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') as statement,
          n.value('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') as database_name
          from (select cast(event_data as XML) as event_data
          from sys.fn_xe_file_target_read_file('D:FolderMySession*.xel', null, null, null)) ed
          cross apply ed.event_data.nodes('event') as q(n)





          share|improve this answer





















          • This sufficed the need . And are these the only fields we have for extended events ? If I need the query plan to be included , How can I do that ?
            – Manjunath
            Nov 5 at 17:09










          • No, these fields are just an example. You should read the data, that is captured from your session. First look at the xml read from the file, then construct appropriate XPath queries to transform it to table (if you need this). There are thousands of things that you can capture with XE, but this isn't related to the "how to read .xel file" question. You can only read what is written in the file, obviously.
            – Andrey Nikolov
            Nov 5 at 17:53










          • I did some investigation on this and now I am able to modify the XPath queries to get what I need for a given specific scenario / requirement . Your comments and answers helped a lot to dive in . This was really helpful
            – Manjunath
            Nov 6 at 5:58












          • I'm glad I was helpful! It will be very appreciated if you accept and upvote my answer.
            – Andrey Nikolov
            Nov 6 at 6:54















          up vote
          0
          down vote



          accepted










          To read .xel files, you can use sys.fn_xe_file_target_read_file function. For example:



          select cast(event_data as XML) as event_data
          from sys.fn_xe_file_target_read_file('D:FolderMySession*.xel', null, null, null)


          Further you may want to parse the returned XML to get the data in table format. To do this, you need to decide what data to extract from the XML and write the appropriate XPath expressions. For example:



          -- You have to know element names and their data types
          select
          n.value('(@name)[1]', 'varchar(50)') as event_name,
          n.value('(@package)[1]', 'varchar(50)') AS package_name,
          n.value('(@timestamp)[1]', 'datetime2') AS [utc_timestamp],
          n.value('(data[@name="duration"]/value)[1]', 'int') as duration,
          n.value('(data[@name="cpu_time"]/value)[1]', 'int') as cpu,
          n.value('(data[@name="physical_reads"]/value)[1]', 'int') as physical_reads,
          n.value('(data[@name="logical_reads"]/value)[1]', 'int') as logical_reads,
          n.value('(data[@name="writes"]/value)[1]', 'int') as writes,
          n.value('(data[@name="row_count"]/value)[1]', 'int') as row_count,
          n.value('(data[@name="last_row_count"]/value)[1]', 'int') as last_row_count,
          n.value('(data[@name="line_number"]/value)[1]', 'int') as line_number,
          n.value('(data[@name="offset"]/value)[1]', 'int') as offset,
          n.value('(data[@name="offset_end"]/value)[1]', 'int') as offset_end,
          n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') as statement,
          n.value('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') as database_name
          from (select cast(event_data as XML) as event_data
          from sys.fn_xe_file_target_read_file('D:FolderMySession*.xel', null, null, null)) ed
          cross apply ed.event_data.nodes('event') as q(n)





          share|improve this answer





















          • This sufficed the need . And are these the only fields we have for extended events ? If I need the query plan to be included , How can I do that ?
            – Manjunath
            Nov 5 at 17:09










          • No, these fields are just an example. You should read the data, that is captured from your session. First look at the xml read from the file, then construct appropriate XPath queries to transform it to table (if you need this). There are thousands of things that you can capture with XE, but this isn't related to the "how to read .xel file" question. You can only read what is written in the file, obviously.
            – Andrey Nikolov
            Nov 5 at 17:53










          • I did some investigation on this and now I am able to modify the XPath queries to get what I need for a given specific scenario / requirement . Your comments and answers helped a lot to dive in . This was really helpful
            – Manjunath
            Nov 6 at 5:58












          • I'm glad I was helpful! It will be very appreciated if you accept and upvote my answer.
            – Andrey Nikolov
            Nov 6 at 6:54













          up vote
          0
          down vote



          accepted







          up vote
          0
          down vote



          accepted






          To read .xel files, you can use sys.fn_xe_file_target_read_file function. For example:



          select cast(event_data as XML) as event_data
          from sys.fn_xe_file_target_read_file('D:FolderMySession*.xel', null, null, null)


          Further you may want to parse the returned XML to get the data in table format. To do this, you need to decide what data to extract from the XML and write the appropriate XPath expressions. For example:



          -- You have to know element names and their data types
          select
          n.value('(@name)[1]', 'varchar(50)') as event_name,
          n.value('(@package)[1]', 'varchar(50)') AS package_name,
          n.value('(@timestamp)[1]', 'datetime2') AS [utc_timestamp],
          n.value('(data[@name="duration"]/value)[1]', 'int') as duration,
          n.value('(data[@name="cpu_time"]/value)[1]', 'int') as cpu,
          n.value('(data[@name="physical_reads"]/value)[1]', 'int') as physical_reads,
          n.value('(data[@name="logical_reads"]/value)[1]', 'int') as logical_reads,
          n.value('(data[@name="writes"]/value)[1]', 'int') as writes,
          n.value('(data[@name="row_count"]/value)[1]', 'int') as row_count,
          n.value('(data[@name="last_row_count"]/value)[1]', 'int') as last_row_count,
          n.value('(data[@name="line_number"]/value)[1]', 'int') as line_number,
          n.value('(data[@name="offset"]/value)[1]', 'int') as offset,
          n.value('(data[@name="offset_end"]/value)[1]', 'int') as offset_end,
          n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') as statement,
          n.value('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') as database_name
          from (select cast(event_data as XML) as event_data
          from sys.fn_xe_file_target_read_file('D:FolderMySession*.xel', null, null, null)) ed
          cross apply ed.event_data.nodes('event') as q(n)





          share|improve this answer












          To read .xel files, you can use sys.fn_xe_file_target_read_file function. For example:



          select cast(event_data as XML) as event_data
          from sys.fn_xe_file_target_read_file('D:FolderMySession*.xel', null, null, null)


          Further you may want to parse the returned XML to get the data in table format. To do this, you need to decide what data to extract from the XML and write the appropriate XPath expressions. For example:



          -- You have to know element names and their data types
          select
          n.value('(@name)[1]', 'varchar(50)') as event_name,
          n.value('(@package)[1]', 'varchar(50)') AS package_name,
          n.value('(@timestamp)[1]', 'datetime2') AS [utc_timestamp],
          n.value('(data[@name="duration"]/value)[1]', 'int') as duration,
          n.value('(data[@name="cpu_time"]/value)[1]', 'int') as cpu,
          n.value('(data[@name="physical_reads"]/value)[1]', 'int') as physical_reads,
          n.value('(data[@name="logical_reads"]/value)[1]', 'int') as logical_reads,
          n.value('(data[@name="writes"]/value)[1]', 'int') as writes,
          n.value('(data[@name="row_count"]/value)[1]', 'int') as row_count,
          n.value('(data[@name="last_row_count"]/value)[1]', 'int') as last_row_count,
          n.value('(data[@name="line_number"]/value)[1]', 'int') as line_number,
          n.value('(data[@name="offset"]/value)[1]', 'int') as offset,
          n.value('(data[@name="offset_end"]/value)[1]', 'int') as offset_end,
          n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') as statement,
          n.value('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') as database_name
          from (select cast(event_data as XML) as event_data
          from sys.fn_xe_file_target_read_file('D:FolderMySession*.xel', null, null, null)) ed
          cross apply ed.event_data.nodes('event') as q(n)






          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 5 at 7:06









          Andrey Nikolov

          1,04119




          1,04119












          • This sufficed the need . And are these the only fields we have for extended events ? If I need the query plan to be included , How can I do that ?
            – Manjunath
            Nov 5 at 17:09










          • No, these fields are just an example. You should read the data, that is captured from your session. First look at the xml read from the file, then construct appropriate XPath queries to transform it to table (if you need this). There are thousands of things that you can capture with XE, but this isn't related to the "how to read .xel file" question. You can only read what is written in the file, obviously.
            – Andrey Nikolov
            Nov 5 at 17:53










          • I did some investigation on this and now I am able to modify the XPath queries to get what I need for a given specific scenario / requirement . Your comments and answers helped a lot to dive in . This was really helpful
            – Manjunath
            Nov 6 at 5:58












          • I'm glad I was helpful! It will be very appreciated if you accept and upvote my answer.
            – Andrey Nikolov
            Nov 6 at 6:54


















          • This sufficed the need . And are these the only fields we have for extended events ? If I need the query plan to be included , How can I do that ?
            – Manjunath
            Nov 5 at 17:09










          • No, these fields are just an example. You should read the data, that is captured from your session. First look at the xml read from the file, then construct appropriate XPath queries to transform it to table (if you need this). There are thousands of things that you can capture with XE, but this isn't related to the "how to read .xel file" question. You can only read what is written in the file, obviously.
            – Andrey Nikolov
            Nov 5 at 17:53










          • I did some investigation on this and now I am able to modify the XPath queries to get what I need for a given specific scenario / requirement . Your comments and answers helped a lot to dive in . This was really helpful
            – Manjunath
            Nov 6 at 5:58












          • I'm glad I was helpful! It will be very appreciated if you accept and upvote my answer.
            – Andrey Nikolov
            Nov 6 at 6:54
















          This sufficed the need . And are these the only fields we have for extended events ? If I need the query plan to be included , How can I do that ?
          – Manjunath
          Nov 5 at 17:09




          This sufficed the need . And are these the only fields we have for extended events ? If I need the query plan to be included , How can I do that ?
          – Manjunath
          Nov 5 at 17:09












          No, these fields are just an example. You should read the data, that is captured from your session. First look at the xml read from the file, then construct appropriate XPath queries to transform it to table (if you need this). There are thousands of things that you can capture with XE, but this isn't related to the "how to read .xel file" question. You can only read what is written in the file, obviously.
          – Andrey Nikolov
          Nov 5 at 17:53




          No, these fields are just an example. You should read the data, that is captured from your session. First look at the xml read from the file, then construct appropriate XPath queries to transform it to table (if you need this). There are thousands of things that you can capture with XE, but this isn't related to the "how to read .xel file" question. You can only read what is written in the file, obviously.
          – Andrey Nikolov
          Nov 5 at 17:53












          I did some investigation on this and now I am able to modify the XPath queries to get what I need for a given specific scenario / requirement . Your comments and answers helped a lot to dive in . This was really helpful
          – Manjunath
          Nov 6 at 5:58






          I did some investigation on this and now I am able to modify the XPath queries to get what I need for a given specific scenario / requirement . Your comments and answers helped a lot to dive in . This was really helpful
          – Manjunath
          Nov 6 at 5:58














          I'm glad I was helpful! It will be very appreciated if you accept and upvote my answer.
          – Andrey Nikolov
          Nov 6 at 6:54




          I'm glad I was helpful! It will be very appreciated if you accept and upvote my answer.
          – Andrey Nikolov
          Nov 6 at 6:54










          Manjunath is a new contributor. Be nice, and check out our Code of Conduct.










           

          draft saved


          draft discarded


















          Manjunath is a new contributor. Be nice, and check out our Code of Conduct.













          Manjunath is a new contributor. Be nice, and check out our Code of Conduct.












          Manjunath is a new contributor. Be nice, and check out our Code of Conduct.















           


          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53147992%2fhow-to-read-extended-events-xel-file%23new-answer', 'question_page');
          }
          );

          Post as a guest




















































































          這個網誌中的熱門文章

          Xamarin.form Move up view when keyboard appear

          Post-Redirect-Get with Spring WebFlux and Thymeleaf

          Anylogic : not able to use stopDelay()