Thanks for pointing that out. I had completely forgotten about encoding certain characters. I can find only 2 that truly need to be converted. You listed 3 but the < was in there twice and while you might have meant >, that character is auto-converted. So the following adaptation of the overly-simplistic CONVERT should work in most cases:

SELECT CONVERT(XML, REPLACE(REPLACE(N'test < & > me', N'&', N'&amp;'), N'<', N'&lt;'))

I say "most cases" as there is a subset of the UCS-2 character set that is not as easily convertible, at least not without a lot of additional REPLACE() functions that realistically won't be used that much. Fortunately the white-space control characters (13 = Carriage Return, 10 = Line Feed, and 9 = Tab) come through just fine.

To make the updated CONVERT more usable / less cumbersome, it can be encapsulated in a UDF as follows:

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

GO

IF (OBJECT_ID('dbo.ShowEntireString') IS NOT NULL)

BEGIN

DROP FUNCTION dbo.ShowEntireString

END

GO

CREATE FUNCTION dbo.ShowEntireString (@String NVARCHAR(MAX))

RETURNS XML

WITH SCHEMABINDING

AS

BEGIN

RETURN CONVERT(XML, REPLACE(REPLACE(@String, N'&', N'&amp;'), N'<', N'&lt;'))

END

GO

So first a simple test:

SELECT dbo.ShowEntireString(N'test < string for & invalid > chars' + NCHAR(13) + NCHAR(10) + N'new line?' + NCHAR(9) + 'and tabbed?')

And now the full test, showing both a) nearly all of the UCS-2 character set represented [only missing 2078 of the 65,535 characters], and b) a sizable string fully represented [in Grid mode].

DECLARE @String NVARCHAR(MAX)

SET @String = N'A' + NCHAR(13) + N'B' + NCHAR(10) + N'C' + NCHAR(9) + N'D'

;WITH cte AS

(

SELECTTOP (65535) ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [TheNumber]

FROMmaster.sys.all_columns sc1

CROSS JOINmaster.sys.all_columns sc2

)

SELECT@String = @String + NCHAR(cte.TheNumber)

FROMcte

WHEREcte.TheNumber BETWEEN 32 AND 65533 -- 0 through 31 error (well, except 9, 10, and 13)

ANDcte.TheNumber NOT BETWEEN 55296 AND 57343 -- invalid in XML

SELECT LEN(@String), DATALENGTH(@String)

-- 63461 and 126922

SET @String = REPLICATE(@String, 20)

SELECT LEN(@String), DATALENGTH(@String)

-- 1269220 and 2538440

SELECT dbo.ShowEntireString(@String)

Can you now find a case where this method does not work? Including the case where the other method does not work? If so, please let me know how to duplicate the test. Thanks.

Take care,

Solomon...