I would like to get the filepath for my extended event event_file and put it inside a variable but I'm stuck.
Here's what I have:
DECLARE @ExpensiveXML XML
SET @ExpensiveXML = (SELECT CAST(xet.target_data AS xml) AS 'XML'
FROM sys.dm_xe_session_targets AS xet
JOIN sys.dm_xe_sessions AS xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = '<my extended event>');
SELECT @ExpensiveXML.query('/EventFileTarget/File')
and it returns:
<File name="MyFilePath.xel" />
I would like to only return MyFilePath.xel without the < File name=.../ > around it.