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[1]','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
Best Answer
The XQuery expression in the
value()
function must return at most one value.Your expression
EventFileTarget/file/@name[1]
is saying that from allEventFileTarget
nodes you want allfile
nodes and from those nodes you want the firstname
attribute.Given this sample XML:
Your expression returns all four filenames.
Change the XQuery to
(EventFileTarget/file/@name)[1]
to get the firstname
attribute with the pathEventFileTarget/file
.With XML that looks like this
The result
(EventFileTarget/file/@name)[1]
isFileName 4
.In your case there can probably only be one
EventFileTarget
node and only onefile
node but SQL Server does not know that so you have to build the XQuery to guarantee only one value returned.(EventFileTarget/file/@name)[1]
.