Sending ‘FOR XML’ into work for the post office

One way that HTML based email bodys have been constructed is by iterating through a cursor and building your HTML with each row, the trouble with this method is that it does not scale very well. In order to over come the lineal growth of a cursor, we can utilise ‘For XML’ and build our HTML content with a set based operation. This following post will compare both methods to show how this can be done.

Firstly we will build a email body using cursors and see how long this takes to run, We are simply going to build an email that lists all customers* in New South Wales, from the Adventureworks database, along with their email address and phone number.

The following SQL will display this:

select
distinct
ltrim(isnull(Title,'') + ' ' + FirstName + ' ' + LastName)CustomerName,
Phone,
EmailAddress
from
Sales.vIndividualCustomer
where
StateProvinceName = 'New South Wales'

This must now be built into a cursor and iterated in order to build our HTML:

Declare @Body as varchar(max)

set @Body = '<html><body>' +
'<h1>NSW Customers</h1>
' + '<table border="1">'

DECLARE Customers Cursor FAST_FORWARD FOR
select
distinct
ltrim(isnull(Title,'') + ' ' + FirstName + ' ' + LastName) CustomerName,
Phone,
EmailAddress
from
Sales.vIndividualCustomer
where
StateProvinceName = 'New South Wales'


Declare @CustomerName varchar(200),
@Phone varchar(25),
@EmailAddress varchar(50)

open Customers
Fetch Next From Customers into @CustomerName, @Phone, @EmailAddress

while @@Fetch_STatus = 0
begin
set
@Body = @body + '<tr> '
+ '<td>' + @CustomerName + '</td>'
+ '<td>' + @Phone + '</td>'
+ '<td>' + @EmailAddress + '</td>'
+ '</tr>'
Fetch Next from Customers into @CustomerName, @Phone, @EmailAddress
end

close Customers
Deallocate Customers

set @body = @body + '</table>'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AdventureWorksProfile',
@recipients='adventureJo@adventure-works.com',
@subject = 'NSW Customers',
@body = @body,
@body_format = 'HTML' ;

The above approach using cursors works as expected and emails our report to the sales manager, however it is not going to scale very well, a more scalable solution is to generate the same html but using a set operation, utilising For XML as below


DECLARE @body varchar(max),
@Subject varchar(255)

SET @body =
N'<html><body><H1>NSW Customers</H1>' +
'<table border="1">' +
'<tr><th>Customer Name</th>' +
'<th>Phone</th>' +
'<th>EmailAddress</th>' +
(select
Distinct
ltrim(isnull(Title,'') + ' ' + FirstName + ' ' + LastName) td, '',
Phone td, '',
EmailAddress td, ''
from
Sales.vIndividualCustomer
where
StateProvinceName = 'New South Wales'
FOR XML PATH('tr')) +
N'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AdventureWorksProfile',
@recipients='adventureJo@adventure-works.com',
@subject = 'NSW Customers',
@body = @body,
@body_format = 'HTML' ;

This code generates the same email many times faster with a lighter impact on our server, moreso, it is scalable as promised.

Advertisements
Categories: For XML, send_DBEmail | 2 Comments

Post navigation

2 thoughts on “Sending ‘FOR XML’ into work for the post office

  1. I posted a solution for building a table using FORXML. This uses FOR XML to avoid embedding strings like the header etc.http://sqlblogcasts.com/blogs/simons/archive/2008/10/28/How-to-make-an-HTML-table-version-2.aspx

  2. thanks for that simon,ill have a look at creating the same table using your method and the post the comparative generation results.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: