Requires A Singleton?

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Have you ever got this error message when you tried to parse an XML string in SQL Server?

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

 
How do we fix this?

This error is saying that the value() function needs an xpath that only exits once (non-repeating node). You and I know that there will only be one instance of , but SQL Server does not know that. To solve this, we just need to tell SQL Server that it should use the first instance of this AnimalName node that it finds. We do this by added the [1] tag to the xpath. We want to add it after the closing parentheses. This will tell SQL Server to grab whatever is the first node with that name.

 
Extra Fun

If there are multiple nodes with the same name in your XML, you can adjust the number inside the square braces to match whatever node you want. For example:

[1] would return Baboon
[2] would return Cat
[3] would return Dog

Leave a Comment

CommentLuv badge