1

I have the following data stored in SQL table and now I want to email this table using msdb.dbo.sp_send_dbmail. How can I include the leading space?

CREATE TABLE #Temp2 (
NewBranchesAdded varchar(1), 
branch_code varchar(10), 
Branch varchar(100))

insert #Temp2 values('y','802',' Chicago, IL')
insert #Temp2 values('y','801','Austin, TX')

Then my code for the SQL stored proc is the following:

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

IF(select top 1 [NewBranchesAdded] from #temp) = 'Y'
BEGIN
SET @xml = CAST(( SELECT branch_code AS 'td','',Branch AS 'td'
FROM  #temp ORDER BY branch_code 
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><body><H3>New Branch(es) added to BI_EDW.dbo.Branch table</H3>
<table border = 1> 
<tr>
<th>BranchCode</th> <th><div align="left">Branch</th></tr>'    

SET @body = @body + @xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MGMTReporting', -- replace with your SQL Database Mail Profile 
@body = @body,
@body_format ='HTML',
@recipients = 'user@abc.com', -- replace with your email address
@subject = 'New Branch Monitor Alert!' ;

END

But the email I received looks like this:

BranchCode  Branch
801 Chicago, IL
802 Austin, TX

So it removes the leading space for Chicago

acornagl
  • 521
  • 5
  • 24
salvationishere
  • 3,461
  • 29
  • 104
  • 143

1 Answers1

3

i figured this out now. SET @body = @body + replace(@xml,' ',' ') +''

salvationishere
  • 3,461
  • 29
  • 104
  • 143
  • Great, that you've found a solution yourself! (+1 from my side). [At this link](https://stackoverflow.com/a/39487565/5089204) I posted an approach to create an `HTML` table directly, wich should be easier... Dealing with XML on string level is rather dangerous... – Shnugo Jan 25 '18 at 07:24