Reading from Extended events XML Column (event_data)

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  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.

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  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