value() Method (xml Data Type)

  • Mikael Eriksson SE

    SSCommitted

    Points: 1688

    Comments posted to this topic are about the item value() Method (xml Data Type)

  • pmadhavapeddi22

    SSCertifiable

    Points: 5306

    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: 258942

    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

  • Michael_Garrison

    Hall of Fame

    Points: 3050

    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: 286958

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

  • Thomas Abraham

    SSChampion

    Points: 10761

    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: 15270

    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

    great question.

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

    Thanks Mikael.

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    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

    Thanks for this nice one, Mikael!

  • Hany Helmy

    SSChampion

    Points: 13321

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

  • Hany Helmy

    SSChampion

    Points: 13321

    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 🙂

  • SQLRNNR

    SSC Guru

    Points: 281210

    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: 17435

    Very interesting question. 🙂

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

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