Post reply

Reading from Extended events XML Column (event_data)

  • April 26, 2017 at 11:46 am

    #329920

    Hi Guys,

    I entered all my extended event contents to a table and trying to extract each column value of the xml column to each columns for a easier read purposes.

    tried to write the code, somewhere seems like an error. Note, I dont think that I need to join with Ring buffer, as anyways, I am putting things to an XEL file.

    Any advise?

    CROSS APPLY targetdata.nodes('//RingBufferTarget/event') AS xed (event_data)) AS xei

    SELECT CAST(event_data as xml) AS targetdata,*
    into #t
    FROM sys.fn_xe_file_target_read_file('C:\temp\[file]*.xel',null, null, null);

    Code -


    SELECT
      event_xml.value('(./@name)', 'varchar(1000)') as event_name,
      event_xml.value('(./data[@name=”database_id”]/value)[1]', 'int') as database_id,
      event_xml.value('(./data[@name=”nt_username”]/value)[1]', 'sysname') as nt_username
      --event_xml.value('(./data[@name=”collect_system_time”]/value)[1]', 'datetime2') as collect_system_time,
      --event_xml.value('(./data[@name=”object_type”]/value)[1]', 'varchar(25)') as object_type,
      --event_xml.value('(./data[@name=”duration”]/value)[1]', 'bigint') as duration,
      --event_xml.value('(./data[@name=”cpu”]/value)[1]', 'bigint') as cpu,
      --event_xml.value('(./data[@name=”row_count”]/value)[1]', 'int') as row_count,
      --event_xml.value('(./data[@name=”reads”]/value)[1]', 'bigint') as reads,
      --event_xml.value('(./data[@name=”writes”]/value)[1]', 'bigint') as writes,
      --event_xml.value('(./action[@name=”sql_text”]/value)[1]', 'varchar(4000)') as sql_text
    FROM  (SELECT CAST(event_data AS XML) xml_event_data
         FROM sys.fn_xe_file_target_read_file('C:\temp\[file]*.xel', NULL, NULL, NULL)) AS event_table
      CROSS APPLY xml_event_data.nodes('//event') n (event_xml)

    Thanks.

  • April 26, 2017 at 12:09 pm

    #1940077

    Your double quotes don't look right they look like ASCII-148's whereas you want ASCII-34's. Try this:

    SELECT
    event_xml.value('(./@name)', 'varchar(1000)') as event_name,
    event_xml.value('(./data[@name="database_id"]/value)[1]', 'int') as database_id,
    event_xml.value('(./data[@name="nt_username"]/value)[1]', 'sysname') as nt_username
    --event_xml.value('(./data[@name="collect_system_time"]/value)[1]', 'datetime2') as collect_system_time,
    --event_xml.value('(./data[@name="object_type"]/value)[1]', 'varchar(25)') as object_type,
    --event_xml.value('(./data[@name="duration"]/value)[1]', 'bigint') as duration,
    --event_xml.value('(./data[@name="cpu"]/value)[1]', 'bigint') as cpu,
    --event_xml.value('(./data[@name="row_count"]/value)[1]', 'int') as row_count,
    --event_xml.value('(./data[@name="reads"]/value)[1]', 'bigint') as reads,
    --event_xml.value('(./data[@name="writes"]/value)[1]', 'bigint') as writes,
    --event_xml.value('(./action[@name="sql_text"]/value)[1]', 'varchar(4000)') as sql_text
    FROM (SELECT CAST(event_data AS XML) xml_event_data
      FROM sys.fn_xe_file_target_read_file('C:\temp\[file]*.xel', NULL, NULL, NULL)) AS event_table
    CROSS APPLY xml_event_data.nodes('//event') n (event_xml);

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply