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 *’
|
1 2 3 4 5 6 7 8 9 10 |
DECLARE @XMLToParse XML -- Load the XML data in to a variable to work with. -- This would typically be passed as a parameter to a stored proc SET @XMLToParse = '<Animal> <AnimalName>Baboon</AnimalName> </Animal>' SELECT @XMLToParse.value('data(/Animal/AnimalName)', 'VARCHAR(MAX)') AS AnimalName |
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
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @XMLToParse XML -- Load the XML data in to a variable to work with. -- This would typically be passed as a parameter to a stored proc SET @XMLToParse = '<Animal> <AnimalName>Baboon</AnimalName> </Animal>' SELECT @XMLToParse.value('data(/Animal/AnimalName)[1]', 'VARCHAR(MAX)') AS AnimalName -- Notice the [1] on the line above! |
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 2 3 4 5 |
<Animal> <AnimalName>Baboon</AnimalName> <AnimalName>Cat</AnimalName> <AnimalName>Dog </AnimalName> </Animal> |
[1] would return Baboon
[2] would return Cat
[3] would return Dog