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

Cohl

What's the ultimate goal? Perhaps XML PATH isn't the PATH of least resistance. – Ariew

Good point. Best way to answer that is to direct you to this previous post: #39455533 – Cohl

Answer 1

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.

Cohl

Answer 2

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

Indonesia

Answer 3

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

Bamako

Answer 4

I suppose since you need to group on the FK you can use something like this... just replace #TempT with your table...

Select Pri.ForeignKey,
       Replace(Left(Pri.Notes,Len(Pri.Notes)-1),',',CHAR(13)) As Notes
From
    (
        Select distinct T2.ForeignKey, 
            (
                Select T1.Note + ',' AS [text()]
                From #TempT T1
                Where T1.ForeignKey = T2.ForeignKey
                ORDER BY T1.ForeignKey
                For XML PATH ('')
            ) Notes
        From #TempT T2
    ) Pri

Also in the OP that you listed in the comments, you have a duplicate PrimaryKey. I found that odd. Just a heads up.

Ariew

Answer 5

If you use below query and results to text option you will see line breaks. Line breaks can't be shown using the results to grid functionality.

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

Principal

Answer 6

I suggest Comment + Char(10) + Char(13)

The "Carriage Return" "Line feed" should be at the end of the line.

Debbradebby

Perhaps I am not fully sure the problem. Xml is just a file. An End of line in Windows is not the same as an end of line in Unix or Mac. In Windows it is Carriage Return and Line Feed. In Unix and Mac is is one of these two characters. For windows you need both Char 10 and 13. Not sure if it is 13 then 10 or 10 then 13. For Mac or Unix you only use one. Osx did it the Unix way older mac OS did it with the other character so even they are not the same. – Debbradebby

This combination of STUFF and SELECT ... FOR XML PATH is a widely seen way to concatenate row data (like group_concat in MySQL). What many people miss (because this works in 99%) is, that this approach cannot deal with special characters... CHAR(10) would work, but CHAR(13) would not... – Indonesia

Answer 7

I believe this could help:

REPLACE(STUFF ((SELECT  CHAR(13)+CHAR(10) + Field1 + Field2
    FROM
    ((table
    WHERE
    field3= 'condition1'
    FOR XML PATH ('')), 1, 0, '') , '
' , '')

Czechoslovakia