Search code examples
sqlsql-server-2008t-sqlfor-xml

Using CHAR(13) in a FOR XML SELECT


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

Solution

  • 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:

    &#x0D;  
    

    (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.