value() Method (xml Data Type)

Question

Post reply

value() Method (xml Data Type)

Mikael Eriksson SE

SSCommitted

Points: 1706
More actions
January 27, 2014 at 8:50 pm

#396173

Comments posted to this topic are about the item value() Method (xml Data Type)
January 28, 2014 at 1:30 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 1

mikael,

Learnt something new today, thanks

I tried with the below solution to get the value from item code

DECLARE @XML as xml='
<root>
<item>1987</item>
<item>1654</item>
<item>1321</item>
</root>';
select @XML.value('/root[1]', 'nvarchar(15)') -- this is returning value as '198716541321'
select @XML.value('/root[1]', 'int') -- this is throwing error

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 2

Interesting question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Michael_Garrison Hall of Fame Points: 3110 More actions · Answer 3

a ha, Finally got my coffee first before answering the question. Studied the question and played around a little. Learned too, thats a great way to start the day off.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 4

Nice questions, thanks. I don't use XML often, but when I do, I use it slowly. 😉

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Thomas Abraham SSChampion Points: 10761 More actions · Answer 5

Ed Wagner (1/28/2014)
Nice questions, thanks. I don't use XML often, but when I do, I use it slowly. 😉

+1 I really liked this question. I don't use XML in my current work. But, this question prompted me to do some exploring. Thanks Mikael!

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

paul.knibbs SSCoach Points: 15320 More actions · Answer 6

I got the right answer but for the wrong reason--I assumed the XQuery expression would return 3 separate values, which would break the requirement for value() to return only one. What can I say, I don't ever use XML and the XQuery documentation is not the best-written I've ever come across! 🙂

PChiragS SSCarpal Tunnel Points: 4965 More actions · Answer 7

great question.

had to do a little research.. but worth it.

Thanks Mikael.

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 8

I had it right, for the wrong reason. I expected an error because the first "root" node contains an XML fragment with multiple nodes, not a singleton. So I expected an error to that effect. I had not expected SQL Server to simply concatenate all values and act is if they are one.

If the question had used bigint instead of int, I would have gotten it wrong.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Revenant SSC-Forever Points: 42467 More actions · Answer 9

Revenant

SSC-Forever

Points: 42467

January 28, 2014 at 9:29 pm

#1684130

Thanks for this nice one, Mikael!

Hany Helmy SSChampion Points: 13921 More actions · Answer 10

Since the beginning I had an issue for understanding XML queries & data type as well, maybe because of incomplete, insufficient documentation for it.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Hany Helmy SSChampion Points: 13921 More actions · Answer 11

Hugo Kornelis (1/28/2014)
I had it right, for the wrong reason. I expected an error because the first "root" node contains an XML fragment with multiple nodes, not a singleton. So I expected an error to that effect. I had not expected SQL Server to simply concatenate all values and act is if they are one.
If the question had used bigint instead of int, I would have gotten it wrong.

+ 2 🙂

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Thomas Abraham (1/28/2014)
Ed Wagner (1/28/2014)
Nice questions, thanks. I don't use XML often, but when I do, I use it slowly. 😉
+1 I really liked this question. I don't use XML in my current work. But, this question prompted me to do some exploring. Thanks Mikael!

+1

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqlnaive SSCoach Points: 17437 More actions · Answer 13

sqlnaive

SSCoach

Points: 17437

February 4, 2014 at 10:55 pm

#1686098

Very interesting question. 🙂