opc.three (6/30/2013)
I only meant to show the two characters that would be escaped when casting to the XML data type.Thanks for the code samples Solomon. I ran a few tests and the one difference between the XML data type and the FOR XML techniques that I saw as important to note is that the XML data type will not show the actual text in all cases, i.e. it escapes the left-angle bracket and ampersand characters as < and & respectively, which could prove to disqualify its usage some scenarios.
Hey there. Yes, the XML data-type does require escaped less-than, ampersand, and even greater-than (these are auto-translated by CONVERT it seems), but I don't necessarily agree that it disqualifies any usage. Let's consider the two options, which truly are very similar:
1) XML datatype: This method requires doing 2 explicit REPLACE functions to translate less-than and ampersand characters into their XML-encoded values. The end result is an XML string that is exactly the string being requested with the only exceptions being the three escaped values: &, <, and >. However, after clicking on the XML value and getting to the "xml" tab, you can do a find-and-replace on these three and end up with the exact string being requested.
2) processing-instruction: This method requires just the special field name "processing-instruction(something)" and the "FOR XML RAW(''), TYPE". The end result is an XML string that has an open tag of "<?something" and a close tag of "?>". The string within those tags is identical (in all but one case) to the string being requested (i.e. no XML-encodings). The one exception to the true string representation is that any occurrences of "?>" in the source string will have a space added between the "?" and ">" characters. This could be problematic in some cases as it is then impossible to determine which occurrences of "? >" are naturally in the source string as opposed to which ones are a result of having the space added by this process.
So, personally I still like the XML datatype method as I have the ability to get back to a true representation of the source string (even if it takes a few hits of Control-H) whereas the processing-instruction method might not, in some cases, be able to get back to the true representation. However, to be fair, it is probably much more likely that strings have <, >, and & characters in them than "?>" sequences so most of the time people would be safe with the processing-instruction method. Proving once again that it is good to have choices :-).
Take care,
Solomon...
PS. It seems that this forum does a double-pass decoding so it is not easy to show HTML / XML -encoded values. Meaning, typing in "&lt;" will actually display ">" instead of '<". You need to instead specify "&amp;lt;". And so you can imagine the silliness required for me to type this up ;-).
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR