April 5, 2016 at 3:51 am
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.
April 5, 2016 at 6:20 am
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
April 5, 2016 at 5:54 pm
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
April 6, 2016 at 5:29 am
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
April 6, 2016 at 8:40 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy