Converting to XML

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714297

    Comments posted to this topic are about the item Converting to XML

  • This was removed by the editor as SPAM

  • Hany Helmy

    SSChampion

    Points: 13291

    Nice question, thanx.

  • George Vobr

    SSCrazy Eights

    Points: 8877

    Thanks Steve for an interesting question. I have tried a variant, the result of which I couldn't answer without running the code in SSMS. Try this:

    SELECT TRY_CONVERT(XML, NULL);

    SELECT TRY_CONVERT(XML, 'NULL');

    SELECT TRY_CONVERT(XML, '[NULL]');

    GO

    -- And with an error:

    SELECT TRY_CONVERT(XML, ['NULL']);

    GO

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Got it, thanks. But to my chagrin we are still on 2008R2.

  • Ed Wagner

    SSC Guru

    Points: 286950

    An interesting question, and I say that as someone who doesn't like working with XML unless I really have to.

  • patricklambin

    SSCrazy Eights

    Points: 9964

    Thanks for this interesting question.

    I have been able to find the good answer only because on last Sunday , I had to fight with the BOL to understand how to use the CONVERT function with XML data. And as usually , I begun by test of TRY_CONVERT and TRY_PARSE.

  • Iulian -207023

    SSCertifiable

    Points: 7507

    Yes, interesting question. Thanks.

  • Toreador

    SSChampion

    Points: 11220

    George Vobr (3/30/2016)


    Try this:

    SELECT TRY_CONVERT(XML, 'NULL');

    The others I understand, but not this one!

  • George Vobr

    SSCrazy Eights

    Points: 8877

    Toreador (3/31/2016)


    George Vobr (3/30/2016)


    Try this:

    SELECT TRY_CONVERT(XML, 'NULL');

    The others I understand, but not this one!

    Hi Toreador

    It just shows that the result with the NULL is the same as 'NULL', nothing more.

    Sincerely G.V.

  • Toreador

    SSChampion

    Points: 11220

    George Vobr (3/31/2016)


    It just shows that the result with the NULL is the same as 'NULL', nothing more.

    I would have expected 'NULL' to be treated as a string with that value, and produce an XML fragment in the same way that it would for 'NULLXX'.

  • George Vobr

    SSCrazy Eights

    Points: 8877

    Toreador (3/31/2016)


    George Vobr (3/31/2016)


    It just shows that the result with the NULL is the same as 'NULL', nothing more.

    I would have expected 'NULL' to be treated as a string with that value, and produce an XML fragment in the same way that it would for 'NULLXX'.

    +1 yes, me too.

  • Ed Wagner

    SSC Guru

    Points: 286950

    George Vobr (3/31/2016)


    Toreador (3/31/2016)


    George Vobr (3/31/2016)


    It just shows that the result with the NULL is the same as 'NULL', nothing more.

    I would have expected 'NULL' to be treated as a string with that value, and produce an XML fragment in the same way that it would for 'NULLXX'.

    +1 yes, me too.

    If you really look at it, the string 'NULL' was treated like somewhere in between those two. The string comes back as the string 'NULL', but isn't really NULL and is not an XML fragment. The actual NULL comes back as actually NULL.

    This will let you see that one's a string and the other is actually NULL. The CASE statement also illustrates that the first isn't actually NULL. The UNION ALL is to produce two rows so you can see the background color of the data set in SSMS.

    WITH cte(x) AS (

    SELECT TRY_CONVERT(XML, 'NULL') UNION ALL

    SELECT TRY_CONVERT(XML, 'NULL')

    )

    SELECT x, CASE WHEN x IS NULL THEN 1 ELSE 0 END

    FROM cte;

    WITH cte(x) AS (

    SELECT TRY_CONVERT(XML, NULL) UNION ALL

    SELECT TRY_CONVERT(XML, NULL)

    )

    SELECT x, CASE WHEN x IS NULL THEN 1 ELSE 0 END

    FROM cte;

    The only thing I see wrong here is that the string wasn't converted into an XML fragment. This makes me wonder what Microsoft is doing under the hood in the conversion, but not enough to actually go investigating it. 😉 I don't particularly care much for XML anyway, but this is a good thing to know.

    Thanks, George, for your interesting point.

  • George Vobr

    SSCrazy Eights

    Points: 8877

    Ed Wagner (3/31/2016)


    George Vobr (3/31/2016)


    Toreador (3/31/2016)


    George Vobr (3/31/2016)


    It just shows that the result with the NULL is the same as 'NULL', nothing more.

    I would have expected 'NULL' to be treated as a string with that value, and produce an XML fragment in the same way that it would for 'NULLXX'.

    +1 yes, me too.

    If you really look at it, the string 'NULL' was treated like somewhere in between those two. The string comes back as the string 'NULL', but isn't really NULL and is not an XML fragment. The actual NULL comes back as actually NULL.

    This will let you see that one's a string and the other is actually NULL. The CASE statement also illustrates that the first isn't actually NULL. The UNION ALL is to produce two rows so you can see the background color of the data set in SSMS.

    WITH cte(x) AS (

    SELECT TRY_CONVERT(XML, 'NULL') UNION ALL

    SELECT TRY_CONVERT(XML, 'NULL')

    )

    SELECT x, CASE WHEN x IS NULL THEN 1 ELSE 0 END

    FROM cte;

    WITH cte(x) AS (

    SELECT TRY_CONVERT(XML, NULL) UNION ALL

    SELECT TRY_CONVERT(XML, NULL)

    )

    SELECT x, CASE WHEN x IS NULL THEN 1 ELSE 0 END

    FROM cte;

    The only thing I see wrong here is that the string wasn't converted into an XML fragment. This makes me wonder what Microsoft is doing under the hood in the conversion, but not enough to actually go investigating it. 😉 I don't particularly care much for XML anyway, but this is a good thing to know.

    Thanks, George, for your interesting point.

    Thank you, Ed, for your reply with an interesting script and his precise explanation.

    Sincerely G.V.

  • Koen Verbeeck

    SSC Guru

    Points: 258854

    Thanks for the question.

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

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

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