asked
Convert SQL data to HTML with sql xml
Hi all, Currently, we are using SQL Server 2012. There we are having a sql table with following schema. CREATE TABLE #temptbl ( year INT , month INT , day INT , high INT , low INT , avg INT ) INSERT INTO #temptbl VALUES ( 2015, 4, 1, 73, 0, 19 ) INSERT INTO #temptbl VALUES ( 2015, 4, 2, 66, 0, 19 ) INSERT INTO #temptbl VALUES ( 2015, 5, 3, 67, 1, 20 ) INSERT INTO #temptbl VALUES ( 2015, 5, 5, 71, 1, 18 ) INSERT INTO #temptbl VALUES ( 2015, 6, 6, 39, 0, 5 ) INSERT INTO #temptbl VALUES ( 2015, 6, 8, 54, 0, 4 ) INSERT INTO #temptbl VALUES ( 2015, 7, 2, 74, 6, 33 ) INSERT INTO #temptbl VALUES ( 2015, 7, 3, 66, 0, 19 ) INSERT INTO #temptbl VALUES ( 2015, 8, 5, 67, 1, 20 ) INSERT INTO #temptbl VALUES ( 2015, 8, 3, 33, 0, 4 ) INSERT INTO #temptbl VALUES ( 2015, 9, 5, 45, 0, 6 ) INSERT INTO #temptbl VALUES ( 2015, 9, 6, 73, 0, 19 ) INSERT INTO #temptbl VALUES ( 2015, 10, 8, 66, 0, 19 ) INSERT INTO #temptbl VALUES ( 2015, 10, 4, 67, 1, 20 ) INSERT INTO #temptbl VALUES ( 2015, 11, 7, 71, 1, 18 ) INSERT INTO #temptbl VALUES ( 2015, 11, 10, 39, 0, 5 ) INSERT INTO #temptbl VALUES ( 2015, 12, 15, 54, 0, 4 ) INSERT INTO #temptbl VALUES ( 2015, 12, 21, 74, 6, 33 ) INSERT INTO #temptbl VALUES ( 2016, 1, 26, 66, 0, 19 ) INSERT INTO #temptbl VALUES ( 2016, 1, 28, 67, 1, 20 ) INSERT INTO #temptbl VALUES ( 2016, 2, 13, 33, 0, 4 ) INSERT INTO #temptbl VALUES ( 2016, 2, 4, 45, 0, 6 ) INSERT INTO #temptbl VALUES ( 2016, 3, 7, 73, 0, 19 ) INSERT INTO #temptbl VALUES ( 2016, 3, 10, 66, 0, 19 ) INSERT INTO #temptbl VALUES ( 2016, 4, 15, 67, 1, 20 ) INSERT INTO #temptbl VALUES ( 2016, 4, 17, 71, 1, 18 ) We need to send the above data as an email report through database mail in HTML format(as shown in the below image). can anyone help us in this regard. Thanks in advance, K.P.Senthil Kumar ![alt text][1]![alt text][2] [1]: /storage/temp/3236-table-structure.png [2]: /storage/temp/3235-required-output.png
This could be a starting point for you. I am using SQL to send it. You can modify the select to meet your requirements: DECLARE @tableHTML NVARCHAR(MAX) ; SET @tableHTML = N'
Daily Receiver Balance Report
' + N'Balance for the current week
' + N'' + N'StoreReceiver Total (HQ)Receiver Total (CORP01DBSVR)Difference (CORP01DBSVR)' + N'Receiver Total (ANALYSIS)Difference (ANALYSIS)' + CAST ( ( SELECT td = CONVERT( VARCHAR(3), s.PRM_STORE_NUMBER) + ' - ' + s.STORE_NAME, '', td = CONVERT(MONEY, SUM(h.rcv_total)), '', td = CONVERT(MONEY, SUM(c.rcv_total)), '', td = CONVERT(MONEY, SUM(h.rcv_total)) - CONVERT(MONEY, SUM(c.rcv_total)), '', td = CONVERT(MONEY, SUM(a.rcv_total)), '', td = CONVERT(MONEY, SUM(h.rcv_total)) - CONVERT(MONEY, SUM(a.rcv_total)), '' FROM ##a_RECEIVER_BALANCE a, ##h_RECEIVER_BALANCE h, BigyHQ_REPL.dbo.STORE_TABLE s, ##c_RECEIVER_BALANCE c WHERE a.store_id = s.STORE_ID AND a.store_id = h.store_id AND a.store_id = c.store_id GROUP BY s.PRM_STORE_NUMBER, s.STORE_NAME ORDER BY s.PRM_STORE_NUMBER FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'' + N'This report is based upon the most recent run of the daily Effective Cost/BBM Updates
' ; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLAlerts', @recipients = 'emailrecipient@domain.COM', @subject = 'Daily Receiver Effective Cost/BBMBalance Report', @body = @tableHTML, @body_format = 'HTML' ;
19 People are following this question.
