View with a concatenated text field with line breaks

Question

Using SQL Server 2012, I need to create a view with a field that concatenates a log field from another table, roughly:

select
    id,
    description,
    STUFF((
        SELECT
            '------------' + char(13) + char(10) +
            MessageLog.MessageText + char(13) + char(10) +
            '- on ' +
                format(MessageLog.MessageDateTime, 'yyyy-MM-dd HH:mm') +
                ' by ' + MessageLog.MessageAuthor + char(13) + char(10)
        FROM
           MessageLog
        WHERE
            Incident.IncidentNumber = MessageLog.IncidentNumber
      ORDER BY MessageLog.MessageDateTime DESC
      FOR XML PATH('')),
    1,
    2,
    ''
    ) AS allMessages
from
    Incident

The line breaks (char(13)+char(10)) come back as hex encoded 
 instead of, well, line breaks. The data from the table gets dumped into an HTML input field and I have no chance for any post-processing.

I have tried storing the line break as just char(13); just char(10); \n; <br />; and just hitting enter between single quotes. Nothing renders correctly. I am guessing that I am abusing STUFF() / XML but cannot figure out where my mistake is.

How can I create my allMessages value with line breaks?

Answer 1

I found this example here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138165

CREATE TABLE #MessageLog
    (
      MessageText VARCHAR(MAX),
      MessageDateTime VARCHAR(MAX),
      MessageAuthor VARCHAR(MAX),
      IncidentNumber INT
    )

CREATE TABLE #Incident
(
id INT,
[description] VARCHAR(30),
IncidentNumber INT
)

INSERT INTO #MessageLog (MessageText, MessageDateTime, MessageAuthor, IncidentNumber  )
SELECT 'masg1 'MessageText, GETDATE()-6 MessageDateTime,'abc' MessageAuthor, 4 IncidentNumber
UNION ALL 
SELECT 'msg 2'MessageText, GETDATE()-6 MessageDateTime,'abc' MessageAuthor, 4 IncidentNumber
UNION ALL 
SELECT 'msg3'MessageText, GETDATE()-6 MessageDateTime,'abc' MessageAuthor, 3 IncidentNumber

INSERT INTO #Incident
SELECT 1, 'desc', 4
UNION ALL 
SELECT 1, 'desc', 4
UNION ALL 
SELECT 1, 'desc', 3

-- the solution
SELECT        STUFF(( SELECT  '------------' + CHAR(13) + CHAR(10)
                        + MessageLog.MessageText + CHAR(13) + CHAR(10)
                        + '- on ' + MessageLog.MessageDateTime + ' by '
                        + MessageLog.MessageAuthor + CHAR(13) + CHAR(10)
                FROM    #MessageLog MessageLog
                WHERE   Incident.IncidentNumber = MessageLog.IncidentNumber
                ORDER BY MessageLog.MessageDateTime DESC
              FOR XML PATH (''),TYPE ).value('.[1]','varchar(max)')
              , 1, 2, '') AS allMessages
FROM    #Incident Incident