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 @tbl TABLE(Field1 INT, Field2 VARCHAR(10), Field3 VARCHAR(10));
INSERT INTO @tbl VALUES
 (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
        )  
FOR XML PATH('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"