value() Method (xml Data Type)
-
Comments posted to this topic are about the item value() Method (xml Data Type)
-
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
-
Interesting question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
This was removed by the editor as SPAM
-
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.
-
Nice questions, thanks. I don't use XML often, but when I do, I use it slowly.
-
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 -
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!
-
great question.
had to do a little research.. but worth it.
Thanks Mikael.
-
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/ -
Thanks for this nice one, Mikael!
-
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 -
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 -
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 -
Very interesting question.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply