XQuery - Singleton Error

value( )  method always returns a scalar value of specified SQL Server data type.

Example: for value ( ) method & how to avoid singleton error.

CREATE DATABASE TEST_XML 

USE TEST_XML
GO
CREATE TABLE College_Master 
(College_ID INT NOT NULL, 
College_Name VARCHAR(20),
College_Details XML)



INSERT INTO College_Master VALUES (1,'BCC UNIVERSITY','<STUDENTINFO>
  <student ID="1" name="Sathya">
    <subject ID="1" Name="Electronics and Communication" />
    <subject ID="2" Name="Circuit Analysis" />
    <subject ID="3" Name="Mobile Communication" />
  </student>
  <student ID="2" name="Deepak">
    <subject ID="4" Name="Data Structure" />
    <subject ID="5" Name="Java" />
    <subject ID="6" Name="Database Management System" />
  </student>
  <student ID="3" name="sathish">
    <subject ID="7" Name="Soil Mechanics" />
    <subject ID="8" Name="Steel Design" />
    <subject ID="9" Name="Concrete Design" />
  </student>
</STUDENTINFO>')

If you execute below query,

SELECT College_Details.value('(/STUDENTINFO/student/@name)','varchar(20)') FROM College_Master
WHERE College_ID = 1;

You will get below error, because value ( ) method always returns a scalar value

Msg 2389, Level 16, State 1, Line 1
XQuery [College_Master.College_Details.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'


In the above example, if you notice XML instance, it has 3 student element nodes,

So to return first student name, execute below query

SELECT College_Details.value('(/STUDENTINFO/student/@name)[1]','varchar(20)') FROM College_Master
WHERE College_ID = 1;

To return second&third student names, execute below queries,

SELECT College_Details.value('(/STUDENTINFO/student/@name)[2]','varchar(20)') FROM College_Master
WHERE College_ID = 1;

SELECT College_Details.value('(/STUDENTINFO/student/@name)[3]','varchar(20)') FROM College_Master
WHERE College_ID = 1;


 --DROP DATABASE TEST_XML

See Also:

• My posts on SQL Server XML