Using CHAR(13) in a FOR XML SELECT

Question

I'm trying to use CHAR(13) to force a new line, but the problem is, I'm doing this within a FOR XML Select statement:

SELECT 
    STUFF((SELECT CHAR(13) + Comment 
FROM 
   myTable 
FOR XML PATH ('')) , 1, 1, '')

The problem with this, is that I don't get an actual new line. Instead, I get:

#x0D;

So the data literally looks like this:

#x0D;First Line of Data#x0D;Second Line of Data#x0D;Etc

So I tried to just replace #x0D; with CHAR(13) outside of the FOR XML:

REPLACE(SELECT 
    STUFF((SELECT CHAR(13) + Comment 
FROM 
   myTable 
FOR XML PATH ('')) , 1, 1, '')), '#x0D;', CHAR(13))

This gets me close. It DOES add in the line breaks, but it also includes an & at the end of each line, and the start of each line after the first:

First Line of Data&
&Second Line of Data&
&Etc

Answer 1

Your approach is not real XML:

Try this with "output to text":

DECLARE @tbl TABLE(TestText VARCHAR(100));
INSERT INTO @tbl VALUES('line 1'),('line 2'),('line 3');

SELECT STUFF
(
    (
        SELECT CHAR(10) + tbl.TestText
        FROM @tbl AS tbl
        FOR XML PATH('')
    ),1,1,''
)

With CHAR(13)

#x0D;line 1
line 2
line 3

See that your STUFF just took away the ampersand?

With CHAR(10)

line 1
line 2
line 3

But what you really need is:

SELECT STUFF
(
    (
        SELECT CHAR(10) + tbl.TestText --you might use 13 and 10 here
        FROM @tbl AS tbl
        FOR XML PATH(''),TYPE
    ).value('.','nvarchar(max)'),1,1,''
)

The ,TYPE will return real XML and with .value() you read this properly.

Some background

You have a misconception of "So the data literally looks like this"

It does not "look like this", it is escaped to fit to the rules within XML. And it will be back encoded implicitly, when you read it correctly.

And you have a misconception of line breaks:

In (almost) ancient times you needed a CR = Carriage Return, 13 or x0D to move back the printing sledge and additionally you needed a LF = Line Feed, 10 or x0A to turn the platen to move the paper. Hence the widely used need to have a line break coded with two characters (13/10 or 0D/0A).

Today the ASCII 10 (0A) is often seen alone...

But back to your actual problem: Why do you bother about the look of your data? Within XML some string might look ugly, but - if you read this properly - the decoding back to the original look is done implicitly...

Your residues are not more than part of the encoding as this starts with an ampersand and ends with a semicolon: &lg; or 
. Your attempt to replace this is just one character to short. But anyway: You should not do this!

Just try:

SELECT CAST('<x>&#x48;&#x65;&#x6c;&#x6c;&#x6f;</x>' AS XML).value('/x[1]','nvarchar(max)')

Answer 2

I think this is cleaner. Basically start with line feeds (or some other special character) then replace them with carriage returns plus line feeds if you want.

Select REPLACE(STUFF((SELECT CHAR(10) + Comment 
                      FROM myTable FOR XML PATH ('')) , 1, 1, ''),
              CHAR(10), CHAR(13)+CHAR(10))