Apr 5, 2012

XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'


If we will execute following sql query in sql server

DECLARE @Data AS XML = '
<Root>
    <Data>
         <Id>5</Id>
         <Name>Scott</Name>
         <Location>USA</Location>
    </Data>
    <Data>
         <Id>6</Id>
         <Name>Greg</Name>
         <Location>USA</Location>
    </Data>
</Root>'

SELECT Data.Col.value('/Root/Data/Id','Int')
FROM @Data.nodes('/Root') AS Data(Col)

We will get error message :

XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

Cause: It is necessary that first parameter of value function must return a single value from XML data.

Solution:

Correct way to write above sql query is:

DECLARE @Data AS XML = '
<Root>
    <Data>
         <Id>5</Id>
         <Name>Scott</Name>
         <Location>USA</Location>
    </Data>
    <Data>
         <Id>6</Id>
         <Name>Greg</Name>
         <Location>USA</Location>
    </Data>
</Root>'


SELECT Data.Col.value('(/Root/Data/Id)[1]','Int')
FROM @Data.nodes('/Root') AS Data(Col)

Output:
1

11 comments:

  1. I tested it (the code) and you only got ID=5 from the xml ¿right? but if I want to get also ID numbre 6 in the same Xquery ¿how can I do it?

    ReplyDelete
    Replies
    1. Use this:

      SELECT Data.Col.value('(Id)[1]','Int') AS Id
      FROM @Data.nodes('/Root/Data') AS Data(Col)

      Delete
    2. Anonymous9/03/2014

      DECLARE @Data AS XML = '


      5
      Scott
      USA


      6
      Greg
      USA

      '


      SELECT
      dat.value('Id[1]','int') AS ID,
      dat.value('Name[1]', 'varchar(50)') AS Name,
      dat.value('Location[1]', 'varchar(150)') AS Location
      FROM @Data.nodes('/Root') AS Data(Col)
      CROSS APPLY @Data.nodes('/Root/Data') AS X(dat)

      Delete
  2. Anonymous2/26/2013

    Thank you so much for this post, this has helped me immensely!

    ReplyDelete
  3. Anonymous6/11/2013

    Seems like the correct answer should be 5, not 1.

    ReplyDelete
  4. Thanks, that worked for me.

    ReplyDelete
  5. Anonymous9/08/2013

    Thanks man! Helped me out!!!!

    ReplyDelete
  6. Great post. Thanks a lot. Helped me fix as well as understand. :)

    ReplyDelete
  7. Anonymous2/20/2015

    Thanks alott

    ReplyDelete