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
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?
ReplyDeleteUse this:
DeleteSELECT Data.Col.value('(Id)[1]','Int') AS Id
FROM @Data.nodes('/Root/Data') AS Data(Col)
DECLARE @Data AS XML = '
Delete5
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)
Thank you so much for this post, this has helped me immensely!
ReplyDeleteSeems like the correct answer should be 5, not 1.
ReplyDeleteThanks, that worked for me.
ReplyDeleteThanks man! Helped me out!!!!
ReplyDeleteGreat post. Thanks a lot. Helped me fix as well as understand. :)
ReplyDeletethanxx
ReplyDeleteThanku very much
ReplyDeleteThanks alott
ReplyDelete