Skip to content Skip to sidebar Skip to footer
Home / Email / Html Table / Sql Server / Xhtml / Xml

Send Email From Sql Server Database With Multiple Tables In Email Body

Post a Comment
I am challenged by the following task. Here is my question in detail: I need to send email from SQL Server with HTML table format that the email body will have several tables. Qu

Solution 1:

Using the function I provide here you can do this:

DECLARE@tblTABLE(Field1 INT, Field2 VARCHAR(10), Field3 VARCHAR(10));
INSERTINTO@tblVALUES
 (1,'AA','Value1')
,(2,'BB','Value1')
,(3,'CC','Value1')
,(1,'OO','Value2')
,(2,'XX','Value2')
,(3,'VV','Value2')
,(1,'qwqw','Value3')
,(2,'GGGG','Value3')
,(3,'COCO','Value3');

--The query will build this as one big XHTML

SELECT (SELECT N'Section: Field3="Value1"' AS p FOR XML PATH(''),TYPE)
,dbo.CreateHTMLTable
        (
        (SELECT * FROM @tbl WHERE Field3='Value1' FOR XML PATH('row'), ELEMENTS XSINIL)
        ,NULL,NULL,NULL
        )
,(SELECT N'Section: Field3="Value2"' AS p FOR XML PATH(''),TYPE)
,dbo.CreateHTMLTable
        (
        (SELECT * FROM @tbl WHERE Field3='Value2' FOR XML PATH('row'), ELEMENTS XSINIL)
        ,NULL,NULL,NULL
        )
,(SELECT N'Section: Field3="Value3"' AS p FOR XML PATH(''),TYPE)
,dbo.CreateHTMLTable
        (
        (SELECT * FROM @tbl WHERE Field3='Value3' FOR XML PATH('row'), ELEMENTS XSINIL)
        ,NULL,NULL,NULL
        )  
FORXMLPATH('body'),ROOT('html');

This is the result (click run code snippet to see the result formatted)

<html><body><p>Section: Field3="Value1"</p><table><thead><tr><th>Field1</th><th>Field2</th><th>Field3</th></tr></thead><tbody><tr><td>1</td><td>AA</td><td>Value1</td></tr><tr><td>2</td><td>BB</td><td>Value1</td></tr><tr><td>3</td><td>CC</td><td>Value1</td></tr></tbody></table><p>Section: Field3="Value2"</p><table><thead><tr><th>Field1</th><th>Field2</th><th>Field3</th></tr></thead><tbody><tr><td>1</td><td>OO</td><td>Value2</td></tr><tr><td>2</td><td>XX</td><td>Value2</td></tr><tr><td>3</td><td>VV</td><td>Value2</td></tr></tbody></table><p>Section: Field3="Value3"</p><table><thead><tr><th>Field1</th><th>Field2</th><th>Field3</th></tr></thead><tbody><tr><td>1</td><td>qwqw</td><td>Value3</td></tr><tr><td>2</td><td>GGGG</td><td>Value3</td></tr><tr><td>3</td><td>COCO</td><td>Value3</td></tr></tbody></table></body></html>

Embedd a <style> node to add CSS formatting

Find further possibilities and background at the link provided above...

Post a Comment for "Send Email From Sql Server Database With Multiple Tables In Email Body"