0

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?

2
  • Have you looked at this question? stackoverflow.com/a/16547390/3187747 Aug 24, 2016 at 13:08
  • @sp_BlitzErik I had seen that question and tried it; I believe my output was screwy because of the web front end and I did not realize where the problem laid. Aug 24, 2016 at 17:28

1 Answer 1

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
7
  • 1
    Thanks to @AA.SC for taking the time to take the link I posted and edit my answer to include a great prototype example! Aug 24, 2016 at 14:19
  • But that's just a prototype. What is the solution?
    – Andriy M
    Aug 24, 2016 at 16:57
  • The solution was to have the FOR XML to include the extra stuff after the FOR XML PATH (''),TYPE ).value('.[1]','varchar(max)') Apparently, that extra information tells XML PATH to honor the line feeds. Aug 24, 2016 at 17:00
  • I'm so sorry, the solution was there, I only needed to scroll down to see it. I think the big gaps between the statements confused me (especially the one before the solution itself). I've taken the liberty of reducing them to just one empty line each.
    – Andriy M
    Aug 24, 2016 at 17:06
  • Can you explain the use of "TYPE" and .value? I am guessing some kind of type casting is going on but could not find references for their use. Aug 24, 2016 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.