I have a column called
target_data in the
sys.dm_xe_session_targets that returns the path where the pertinent file resides in the format of xml tags.
This data is not in XML datatype. One has to cast it to the XML data type.
This is how the typical field in the column looks like:
<EventFileTarget truncated="0"> <Buffers logged="135" dropped="0"/> <File name="C:\ExtendedEvents\degree_of_par\deg_of_par_0_130897981436560000.xel"/> </EventFileTarget>
I want to retrieve the
name attribute of the
file tag i.e.
C:\ExtendedEvents\degree_of_par\deg_of_par_0_130897981436560000.xel in this case.
I tried the following script for the same, however it is throwing errors:
select xe.m.value('EventFileTarget/file/@name','nvarchar(500)'),* from sys.dm_xe_session_targets as s cross apply (select cast(target_data as xml) as m) as xe
The error reads :
Msg 2389, Level 16, State 1, Line 1 XQuery [xe.m.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
I'm perplexed. Please help