SQL mail html table formatting in Outlook

Question

SQL mail html table formatting in Outlook

sfletcher-933270

Right there with Babe

Points: 784
More actions
April 5, 2016 at 3:51 am

#399976

Hi I have stored procedure to send data in table format to Outlook. My code works well in Explorer giving the table header rows and columns as expected. Should look like:
Data1 as Header
Data2 Data3 Data4
When sent to Outlook though the table looses formatting and looks like this:
Data1 as Header
Data2
Data3
Data4
DECLARE @HTMLTable varchar(max);
DECLARE @TableHead varchar(max);
DECLARE @TableTail varchar(max);
SET @TableHead = '<html><body><table>' ;
SET @TableTail = '</table></body></html>';
SET @HTMLTable = @TableHead +(
select '3' as
,Data1 as th,
'' as tr, null,
Data2 as td, null,
Data3 AS td, null,
Data4 as td, null
from @Table
order by Data1, Data2
for xml PATH('tr'), elements
)
set @HTMLTable = @HTMLTable + @TableTail;
Grateful for help. It is frustrating as it looks just fine in IE.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you've got quite a few things going that are causing malformed html; IE will do it's best to clean that up, but Outlook does not.

i populated your table and reviewed the results like this:

DECLARE @Table TABLE(Data1 varchar(30),Data2 varchar(30),Data3 varchar(30),Data4 varchar(30) )

insert into @Table

select top 10 OBJECT_ID,name,type_desc,name from sys.objects

--your original code

SELECT @HTMLTable

if you do that, you get results like this:

an unclosed TR after the TH, ghost TR pairs with no contents.

you are selecting a null without an alias after each column, why?

why didn't you build column names in the @tableHeader?

you are selecting four columns

i think you are trying to select a colspan row as an inline header, and then the three data elements, so it's just going to be fiddling with the formatting.

<html><body><table><html><body><table>

<tr><th colspan="3">17</th>

<tr></tr><td>syspriorities</td><td>SYSTEM_TABLE</td><td>syspriorities</td></tr>

<tr><th colspan="3">19</th>

<tr></tr><td>sysfgfrag</td><td>SYSTEM_TABLE</td><td>sysfgfrag</td></tr>

<tr><th colspan="3">23</th>

<tr></tr><td>sysphfg</td><td>SYSTEM_TABLE</td><td>sysphfg</td></tr>

<tr><th colspan="3">24</th>

<tr></tr><td>sysprufiles</td><td>SYSTEM_TABLE</td><td>sysprufiles</td></tr>

<tr><th colspan="3">25</th>

<tr></tr><td>sysftinds</td><td>SYSTEM_TABLE</td><td>sysftinds</td></tr>

<tr><th colspan="3">27</th>

<tr></tr><td>sysowners</td><td>SYSTEM_TABLE</td><td>sysowners</td></tr>

<tr><th colspan="3">3</th>

<tr></tr><td>sysrscols</td><td>SYSTEM_TABLE</td><td>sysrscols</td></tr>

<tr><th colspan="3">5</th>

<tr></tr><td>sysrowsets</td><td>SYSTEM_TABLE</td><td>sysrowsets</td></tr>

<tr><th colspan="3">7</th>

<tr></tr><td>sysallocunits</td><td>SYSTEM_TABLE</td><td>sysallocunits</td></tr>

<tr><th colspan="3">8</th>

<tr></tr><td>sysfiles1</td><td>SYSTEM_TABLE</td><td>sysfiles1</td></tr></table></body></html></table></body></html>

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sfletcher-933270 Right there with Babe Points: 784 More actions · Answer 2

Hi Lowell

Thank you very much for your reply. I understand there is lots wrong with my code but I don't know how to fiddle with the formatting to get the result I want:-). I see that the unclosed TR after the TH causes a ghost TR pair - I added it there because I think that I need to have a TR around the TH but the way I have written it doesn't work. I didn't build the column headers in the @tableHeader because I don't know how - when I have done this I get the tableHeader row like a separate table to the data rows.

I am trying to select a colspan row as an inline header and then three data elements - if you can suggest how to fiddle with the html I will be forever grateful. Kind regards, Sally

Lowell SSC Guru Points: 323518 More actions · Answer 3

for xml won't let you inject closing html tags any place you want, but what you want to do is make it do the 99% of the work, and then fix it with a simple find and replace.

your code, barely modified like this, and featuring a replace to change the closing TH tag to end its own TR and Start a new TRfor the related data,, seems to do what you were after:

DECLARE @Table TABLE(Data1 varchar(30),Data2 varchar(30),Data3 varchar(30),Data4 varchar(30) )

insert into @Table

select top 10 name,OBJECT_ID,type_desc,name from sys.objects

DECLARE @HTMLTable varchar(max);

DECLARE @TableHead varchar(max);

DECLARE @TableTail varchar(max);

SET @TableHead = '<html><body><table border = "1">' ;

SET @TableTail = '</table></body></html>';

SET @HTMLTable = (

select '3' as

, Data1 as

,null,

Data2 as

, null,

Data3 AS

, null,

Data4 as

, null

from @Table

order by Data1, Data2

For XML path('tr'), Elements)

set @HTMLTable = @TableHead + @HTMLTable + @TableTail;

SELECT @HTMLTable = REPLACE(@HTMLTable,'</th>','</th></tr><tr>')

SELECT @HTMLTable

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sfletcher-933270 Right there with Babe Points: 784 More actions · Answer 4

Hi Lowell

Thank you so much for this. I had made some changes that "worked" but still had issues with my HTML. Your solution works perfectly and produces the expected HTML.

Kind regards, Sally