Using CHAR(13) in a FOR XML SELECT

Thanks everyone for your help.

The ultimate goal here was to present the data in Excel as part of a report. I'm sure there is a more elegant way to do this, but I at least got the results I wanted by doing this:

REPLACE (
    REPLACE(
        REPLACE(
            (SELECT Comment FROM CallNotes WHERE ForeignId = a.ForeignId FOR XML PATH (''))
        , '<Comment>', '')  
    , '</Comment>', CHAR(13) + CHAR(10))
, '&#x0D;', '') AS Comments

The select statement all by itself returns XML as we would expect:

<comment>This is a comment</comment><comment>This is another comment</comment>

The inner most REPLACE just gets rid of the opening tag:

<comment> 

The middle REPLACE removes the closing tag:

</comment> 

and replaces it with CHAR(13) + CHAR(10). And the outer most REPLACE gets rid of this:


  

(I still don't understand where that's coming from.)

So, when the results are sent to Excel, it looks like this inside the cell:

This is a comment.
This is another comment.

Which is exactly what I want. Again, I'm sure there is a better solution. But this at least is working for now.

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)')

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))