Using Xquery to query Extended Events asynchronous file target results

Using Xquery to query Extended Events asynchronous file target results

  • Comments 1

This is the follow up (part 2) to the following post:

http://blogs.msdn.com/grahamk/archive/2009/09/23/realistic-troubleshooting-example-of-extended-events-xevent-usage-in-sql-server-2008-part-1.aspx

In the original post we configured an extended event session and issued a very simple query to review the results as XML. Here are some more intelligent XQuery examples. I’m documenting these purely because I hate XQuery so much, and it takes so damn long to do (well it does for me anyway). Please feel free to post in the comments section if you think XQuery is the best damn query language ever invented :-)

Hopefully the code comment explain everything, I have attempted to use very descriptive alias names as well

-- get information from XET binary files representing extended event session date
 
-- as per part 1 blog post:
-- straight select, view the XML manually in an SSMS window by clicking on the XML results column
select CAST(event_data as XML) , * 
from sys.fn_xe_file_target_read_file
(
'C:\temp\ArithmeticErrors_0_128981071730370000.xet', 
'C:\temp\ArithmeticErrors_0_128981071730390000.xem', 
null, 
null
)
 
-- Get only specific nodes
-- 2 different methods shown of the xquery, you can specify the name of the node
-- or you can specify its location in the structure
-- this means that you return every row for every event captured in the file
-- the results are then converted to SQL data types
 
SELECT
xdata.value('(/event/data[4]/value)[1]', 'varchar(100)') AS [error],
xdata.value('(/event/data[@name="severity"]/value)[1]', 'int') AS [severity],
xdata.value('(/event/action[@name="sql_text"]/value)[1]', 'varchar(max)') AS [sql_text]
 
FROM
    (
        select CAST(event_data as XML)
        from sys.fn_xe_file_target_read_file
        (
        'C:\temp\ArithmeticErrors_0_128981071730370000.xet', 
        'C:\temp\ArithmeticErrors_0_128981071730390000.xem', 
        null, 
        null
        )
    ) as xmlr(xdata)
 
-- using the above technique, you can now wrap the entire query and give it an alias
-- and then perform more useful T-SQL language queries on the results
-- here is an example of an aggregate
 
SELECT
convert(varchar(100), outer_results.[error]) as 'error',
convert(int, outer_results.[severity]) as 'severity',
convert(varchar(max), outer_results.[sql_text]) as 'sql_text',
count(*) as [count_occurances]
FROM
    (
    SELECT
    xdata.value('(/event/data[4]/value)[1]', 'varchar(100)') AS [error],
    xdata.value('(/event/data[@name="severity"]/value)[1]', 'int') AS [severity],
    xdata.value('(/event/action[@name="sql_text"]/value)[1]', 'varchar(max)') AS [sql_text]
 
    FROM
        (
            select CAST(event_data as XML)
            from sys.fn_xe_file_target_read_file
            (
            'C:\temp\ArithmeticErrors_0_128981071730370000.xet', 
            'C:\temp\ArithmeticErrors_0_128981071730390000.xem', 
            null, 
            null
            )
        ) as xmlr(xdata)
    ) as outer_results
group by error, severity,sql_text
 
-- finally using the same technique as above , here we have simple predicates as well
 
-- add a predicate into the outer query
SELECT
convert(varchar(100), outer_results.[error]) as 'error',
convert(int, outer_results.[severity]) as 'severity',
convert(varchar(max), outer_results.[sql_text]) as 'sql_text',
count(*) as [count_occurances]
FROM
    (
    SELECT
    xdata.value('(/event/data[4]/value)[1]', 'varchar(100)') AS [error],
    xdata.value('(/event/data[@name="severity"]/value)[1]', 'int') AS [severity],
    xdata.value('(/event/action[@name="sql_text"]/value)[1]', 'varchar(max)') AS [sql_text]
 
    FROM
        (
            select CAST(event_data as XML)
            from sys.fn_xe_file_target_read_file
            (
            'C:\temp\ArithmeticErrors_0_128981071730370000.xet', 
            'C:\temp\ArithmeticErrors_0_128981071730390000.xem', 
            null, 
            null
            )
        ) as xmlr(xdata)
    ) as outer_results
 
-- here is the extra predicate we have added
where outer_results.severity < 17
group by error, severity,sql_text
 

I fully accept that this may not be the most reasonable way, or the most efficient way to do this, but it’s the way that I’ll be using for now.

Leave a Comment
  • Please add 6 and 1 and type the answer here:
  • Post
  • Thanks for this example Graham, it's going to be really useful for me. I hate XQuery to by the way!

    Could you tell me how do you get the file target info from an existing session using TSQL?

    @fatherjack

Page 1 of 1 (1 items)