May 10, 2016 at 5:53 pm
Hi, I am not sure if this question belongs here, sorry if I am posting in the wrong sub-forum.
I have the below query to search the results of my Extended Events output, but all I get back is NULL. When I run the sub-query, I can see the SQL statements in the XML output, but for some reason I can't extract the statements. Any idea what I need to do to make it work? I am out of ideas. Thank you.
SELECT
data.value (
'(/event/action[@name=''statement'']/value)[1]', 'varchar(max)') AS [SQLText]
FROM
(SELECT CONVERT (XML, event_data) AS data FROM sys.fn_xe_file_target_read_file
('E:\CPU_Pressure*.xel', 'E:\CPU_Pressure*.xem', null, null)
) entries
May 10, 2016 at 7:55 pm
This is probably the best sub forum to post this question provided you are running SQL 2012. There's also an XML sub forum but, since this is a SQL/XML question this one is fine.
Anyhow, this should be a simple problem to solve. Try this:
SELECT
data.value (
'(/event/action[@name="statement"]/value)[1]', 'varchar(max)') AS [SQLText]
FROM
(SELECT CONVERT (XML, event_data) AS data FROM sys.fn_xe_file_target_read_file
('E:\CPU_Pressure*.xel', 'E:\CPU_Pressure*.xem', null, null)
) entries
(I changed the single-quotes around "statement" to double-quotes).
If that does not help then can you post the XML? It's been awhile since I've worked with extended events but I think you can get the XML by changing
data.value (
'(/event/......
to
data.query('.')
-- Itzik Ben-Gan 2001
May 10, 2016 at 9:23 pm
With Alan's correction on the quotation and an additional correction in the value path then this works
😎
SELECT
data.value (
'(/event/data [@name="statement"]/value)[1]', 'varchar(max)') AS [SQLText]
FROM
(SELECT CONVERT (XML, event_data) AS data FROM sys.fn_xe_file_target_read_file
('E:\CPU_Pressure*.xel', 'E:\CPU_Pressure*.xem', null, null)
) entries
If you are running on SQL 2012 or later then the mdpath parameter (2nd) is not needed, it is for backward compatibility.
May 11, 2016 at 12:01 pm
Thank you guys! The second code worked like a charm, thank you so much!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply