How to extract xml attribute from a column?

Question

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

Answer 1

The XQuery expression in the value() function must return at most one value.

Your expression EventFileTarget/file/@name[1] is saying that from all EventFileTargetnodes you want all file nodes and from those nodes you want the first name attribute.

Given this sample XML:

<EventFileTarget>
  <file name = "FileName 1"/>
  <file name = "FileName 2"/>
</EventFileTarget>
<EventFileTarget>
  <file name = "FileName 3"/>
  <file name = "FileName 4"/>
</EventFileTarget>

Your expression returns all four filenames.

Change the XQuery to (EventFileTarget/file/@name)[1] to get the first name attribute with the path EventFileTarget/file.

With XML that looks like this

<EventFileTarget>
  <file/>
  <file/>
</EventFileTarget>
<EventFileTarget>
  <file/>
  <file name = "FileName 4"/>
</EventFileTarget>

The result (EventFileTarget/file/@name)[1] is FileName 4.

In your case there can probably only be one EventFileTarget node and only one file 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].