Line breaks in HTML tables

Question

Hello I am using TSQL and an HTML table to run a query and send the results via an email. I cannot preserve the line breaks in the result set.

I have some test code below. When I run the query via management studio I get the results;

line

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Line 1

Line 2

Line 3

But the email output shows;

Line 1 Line 2 Line 3

Can I preserve the line breaks in sp_sendmail?

code below -
/////////////////////////////////////////////////////

create table test (line nvarchar(max))
go
declare @testtext nvarchar(max)
set @testtext = 'Line 1' + char (10)+ CHAR(13) + 'Line 2' + char (10)+ CHAR(13) + 'Line 3'

select @testtext

insert into test select @testtext

declare @tableHTML nvarchar(max)
SET @tableHTML =
N'<H1>TEST</H1>' +
N'<tr>
<th>test</th>'+
'</tr>'
+
CAST ( (select
td =t.line, ' '
from
test t
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail

@recipients = 'me@myemailaddress',  
@subject = 'test',  
  
@body_format = 'HTML' ,  

@body = @tableHTML ;  

go
select * from test
drop table test

///////////////////////////////////////////////////

Answer 1

If possible, place the "Line 1", "Line 2" etc. on separate rows of test table. Or try one of workaround:

SET @tableHTML =
N'<H1>TEST</H1>
<tr><th>test</th></tr>' +
replace(CAST ( (select td = line from test FOR XML PATH('tr') ) AS NVARCHAR(MAX) ), '&#x0D;', '<br/>') +
N'</table>'

(If test contains several rows, you must use some ORDER BY to send the lines in the right order).

Answer 2

Notes 1 :Use only CHAR(13)

declare @testtext nvarchar(max)
set @testtext = 'Line 1' + CHAR(13) + 'Line 2' + CHAR(13) + 'Line 3'

select @testtext

Notes 2 : Have check setting

Answer 3

Great thank you

Answer 4

Just to be clear.

HTML ignores CHAR(10) and CHAR(13) when displaying to the screen. To create HTML line breaks you must use the HTML code "<br>".

The code Viorel posted simply replaces CHAR(10) with "<br>".

The simpler answer it to not put in the CHAR(10)+CHAR(13) in your string at all, if you are going to use it for HTML output.

set @testtext = 'Line 1' + '<br>' + 'Line 2' + '<br>' + 'Line 3'