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:


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.


Use value() instead.

The value() function returns a scalar value and takes two parameters. The first is the xpath expression and the second is the data type you want.

@ExpensiveXML.value('(/EventFileTarget/File/@name)[1]', 'nvarchar(260)');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.