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?