March 29, 2016 at 10:06 pm
Comments posted to this topic are about the item Converting to XML
March 30, 2016 at 12:04 am
This was removed by the editor as SPAM
March 30, 2016 at 2:39 am
Nice question, thanx.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
March 30, 2016 at 6:08 am
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
March 30, 2016 at 6:37 am
Got it, thanks. But to my chagrin we are still on 2008R2.
March 30, 2016 at 9:44 am
An interesting question, and I say that as someone who doesn't like working with XML unless I really have to.
March 30, 2016 at 4:14 pm
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.
March 30, 2016 at 11:23 pm
Yes, interesting question. Thanks.
March 31, 2016 at 1:46 am
George Vobr (3/30/2016)
Try this:
SELECT TRY_CONVERT(XML, 'NULL');
The others I understand, but not this one!
March 31, 2016 at 3:57 am
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.
March 31, 2016 at 4:07 am
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'.
March 31, 2016 at 4:33 am
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.
March 31, 2016 at 5:32 am
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.
March 31, 2016 at 2:40 pm
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.
April 4, 2016 at 6:56 am
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 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply