SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Display More Than 8000 Characters (SQL Spackle)


Display More Than 8000 Characters (SQL Spackle)

Author
Message
Solomon Rutzky
Solomon Rutzky
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1721 Visits: 2965
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/
jim.riedemann
jim.riedemann
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 202
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.
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14929 Visits: 14396
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
Solomon Rutzky
Solomon Rutzky
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1721 Visits: 2965
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/
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14929 Visits: 14396
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
Attachments
51d1bbe2.jpg (105 views, 6.00 KB)
51d1bddc.jpg (101 views, 38.00 KB)
SQLQuery16.sql.txt (24 views, 825 bytes)
Solomon Rutzky
Solomon Rutzky
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1721 Visits: 2965
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/
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14929 Visits: 14396
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85929 Visits: 41091
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search