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.