Returning XML Value for Extended Event

Question

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.

Answer 1

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)');