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:
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?
Use this:
SELECT Data.Col.value('(Id)[1]','Int') AS Id
FROM @Data.nodes('/Root/Data') AS Data(Col)
Thank you so much for this post, this has helped me immensely!
Seems like the correct answer should be 5, not 1.
Thanks, that worked for me.
Thanks man! Helped me out!!!!
Great post. Thanks a lot. Helped me fix as well as understand. :)
thanxx
Thanku very much
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)
Thanks alott
Post a Comment