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:

Proyeccionista said...

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?

Priyanka kumari said...

Use this:

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

Anonymous said...

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

Anonymous said...

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

Unknown said...

Thanks, that worked for me.

Anonymous said...

Thanks man! Helped me out!!!!

Vikas said...

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

Unknown said...

thanxx

ramesh said...

Thanku very much

Anonymous said...

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)

Anonymous said...

Thanks alott