How to extract xml attribute from a column?
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:



&lt;EventFileTarget&gt;
  &lt;file name = "FileName 1"/&gt;
  &lt;file name = "FileName 2"/&gt;
&lt;/EventFileTarget&gt;
&lt;EventFileTarget&gt;
  &lt;file name = "FileName 3"/&gt;
  &lt;file name = "FileName 4"/&gt;
&lt;/EventFileTarget&gt;


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



&lt;EventFileTarget&gt;
  &lt;file/&gt;
  &lt;file/&gt;
&lt;/EventFileTarget&gt;
&lt;EventFileTarget&gt;
  &lt;file/&gt;
  &lt;file name = "FileName 4"/&gt;
&lt;/EventFileTarget&gt;


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].