Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
slihp
Yak Posting Veteran
61 Posts |
 Posted - 2009-01-12 : 09:42:10
|
| Hi allI have the created the script below that get a list of users from a temp table and builds an xml string that is used to create a HTML message for e-mailing to the users. My problem is formating the result set. I can format the elements outwith the table and also the table header but i am having big trouble formating the acutal data returned from the reslut set .i.e. i would like to create a link for each row on the fly using the MatterCode value, ot change the cell background of BilledWip if it is over a certain value.any ideas much appreciated --build the html DECLARE @xml NVARCHAR(MAX) DECLARE @body NVARCHAR(MAX) SET @xml=CAST(( SELECT ISNULL(MatterCode,'-') AS 'td', '', ISNULL(MatterDescription, '-') AS 'td','', CAST(ISNULL(EstimatedWip,0) AS VARCHAR(10)) AS 'td','', CAST(ISNULL(BilledWip,0) AS VARCHAR(10)) AS 'td','', CAST(ISNULL(UnbilledWip,0) AS VARCHAR(10)) AS 'td','', ISNULL(OtherReference,'-') AS 'td','', ISNULL(OtherRef2,'-') AS 'td' FROM #WipAlerts WHERE EarnerDiwor=@EarnerDiwor ORDER BY MatterCode FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) SET @body = '<html> <H1>Alerts</H1> <body bgcolor=white> <table border = 1> <tr> <th>Matter Code</th> <th>Matter Description</th> <th>Estimated Wip</th> <th>Billed Wip</th> <th>Unbilled Wip</th> <th>OtherReference</th> <th>OtherRef2</th> </tr>' SET @body = @body + @xml + '</table> </body> </html>' --send the mail EXEC msdb.dbo.sp_send_dbmail @profile_name = 'GLW-SQL-01',@recipients = @EarnerEmail, @body = @body,@body_format ='HTML',@subject ='Wip Alerts' |
|
|
|
|
|
|
|
XML Formating