Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567

Display More Than 8000 Characters (SQL Spackle) Expand / Collapse
Author
Message
Posted Monday, July 1, 2013 11:46 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, September 27, 2014 8:10 PM
Points: 368, Visits: 1,948
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 "&amp;lt;" will actually display ">" instead of '&lt;". You need to instead specify "&amp;amp;lt;". And so you can imagine the silliness required for me to type this up .





SQL# - http://www.SQLsharp.com/
Post #1469174
Posted Monday, July 1, 2013 12:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 19, 2014 6:56 AM
Points: 15, Visits: 145
That's the config for the "Results to Grid" display. The "Results to Text" config dialog has a different setting, "Maximum number of characters displayed in each column:". The max value on this dialog is 8192.

Post #1469188
Posted Monday, July 1, 2013 1:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
Points: 7,107, Visits: 12,661
Solomon Rutzky (7/1/2013)
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.

To each their own

SELECT CAST('&gt;>' AS XML) AS [Search and Replace?];


PS I can appreciate the trouble you went to in creating your post so things would appear as you intended. I end up using the HTML hex codes on a regular basis to get the output I need, which I had to do for this post as well.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1469201
Posted Monday, July 1, 2013 4:33 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, September 27, 2014 8:10 PM
Points: 368, Visits: 1,948
opc.three (7/1/2013)

SELECT CAST('&gt;>' AS XML) AS [Search and Replace?];



Well, yes and no .

Yes = in that scenario it would be impossible to get back to the source string.

No = that scenario is not what happens with the code I submitted previously since the ampersand would have been escaped, leaving "&amp;gt;&gt;", which could be translated back as long as you do the "&amp;" -> "&" replacement last.

PS I can appreciate the trouble you went to in creating your post so things would appear as you intended. I end up using the HTML hex codes on a regular basis to get the output I need, which I had to do for this post as well.


It seems that maybe the real issue is that the "&amp;" gets translated when posting. If you look at your quote of my text in your last posting, it has the actual <, >, and & characters instead of the encoded values from my post. Your usage of "&#38;" seems to survive longer so I will switch to using that instead of "&amp;". Thanks for the suggestion!

Take care,
Solomon...





SQL# - http://www.SQLsharp.com/
Post #1469236
Posted Monday, July 1, 2013 5:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
Points: 7,107, Visits: 12,661
I think you have added an abstraction layer that seems to work but at the end of the day you still have an entitization problem:





I think your approach has the problem of not differentiating when a character should be entitized, i.e. when a left-angle bracket is part of some markup or a comment versus when &lt; is part of a string literal, however the XML parser within SQL Server that is invoked when you use TYPE as part of FOR XML does know that and it can do that entitization for us properly.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato


  Post Attachments 
51d1bbe2.jpg (87 views, 6.32 KB)
51d1bddc.jpg (85 views, 38.54 KB)
SQLQuery16.sql.txt (3 views, 825 bytes)
Post #1469244
Posted Monday, July 1, 2013 9:30 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, September 27, 2014 8:10 PM
Points: 368, Visits: 1,948
opc.three (7/1/2013)
I think you have added an abstraction layer that seems to work but at the end of the day you still have an entitization problem:


My problem seems to have been more of communication than entitization: I left too much to implication when I said 'do the conversion from "&amp;" -> "&" last'. I should have been clearer about the need to translate the other two encoded values -- &lt; and &gt; -- FIRST. Since there are 3 values that are encoded, those same 3 values need to be decoded. And, it is just as import to encode the & first as it is to decode it last.

So, to re-run the test:

DECLARE @Before NVARCHAR(MAX),
@After NVARCHAR(MAX);

SET @Before = N'&amp;gt;&amp;&gt;>';

SET @After = REPLACE(
CAST(dbo.ShowEntireString(@Before) AS NVARCHAR(MAX)),
N'&amp;',
N'&'
);

-- not the same, but due to missing a REPLACE on &gt;
SELECT @Before AS [Before], @After AS [After]

SET @After =
REPLACE(
REPLACE(
REPLACE(
CAST(dbo.ShowEntireString(@Before) AS NVARCHAR(MAX)),
N'&gt;',
N'>'
),
N'&lt;',
N'<'
),
N'&amp;',
N'&'
);

-- same values
SELECT @Before AS [Before], @After AS [After]

Take care,
Solomon...





SQL# - http://www.SQLsharp.com/
Post #1469280
Posted Tuesday, July 2, 2013 2:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
Points: 7,107, Visits: 12,661
OK, now we're getting somewhere. So three replacements after the conversion to XML, and in a specific order to unwind the original encoding. Thanks for clarifying Solomon.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1469350
Posted Wednesday, October 30, 2013 6:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:14 PM
Points: 35,266, Visits: 31,756
Sorry... post deleted... I had already addressed the issue I was posting about.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1509987
« Prev Topic | Next Topic »

Add to briefcase «««34567

Permissions Expand / Collapse