sp_send_dbmail / XML - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sp_send_dbmail / XML

Author

Topic

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2008-07-21 : 14:43:40

Hi. I'll start by saying I have zero experience with XML, which probably explains my trouble.

I am trying to set up a job that emails the results of a query. It currently is satisfactory, I'd like to know how to make it a little different.

Here is the SP that the job executes:

ALTER PROCEDURE [dbo].[NewResidentNotify]
@SinceDaysAgo int = 1
AS
BEGIN

SET NOCOUNT ON;
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =

N'<table width="300" border="0" cellspacing="1" cellpadding="1">' +
N'<tr>---------------------------------</tr>' +
CAST ( ( SELECT DISTINCT
tr = + Firstname + ' ' + LastName, '',
tr = ' DOB: ' + Convert(varchar,DateofBirth,101), '',
tr = ' DOP: ' + Convert(varchar,DateofPlacement,101),'',
tr = ' County: ' + Lookup_County.CountyName, '',
tr = ' Agency: ' + Lookup_Agency.AgencyName, '',
tr = ' Ethnicity: ' + Lookup_Ethnicity.Ethnicity, '',
tr = Lookup_IDTYPE.TypeName + ': ' + Res_Identity_Number.ID_CODE, '',
tr = '---------------------------------'
FROM dbo.Resident INNER JOIN
dbo.Lookup_Agency ON dbo.Resident.AgencyID = dbo.Lookup_Agency.AgencyID INNER JOIN
dbo.Lookup_County ON dbo.Resident.CountyID = dbo.Lookup_County.CountyID INNER JOIN
dbo.Res_Identity_Number ON dbo.Resident.ResidentID = dbo.Res_Identity_Number.ResidentID INNER JOIN
dbo.Lookup_IDTYPE ON dbo.Res_Identity_Number.ID_TYPEID = dbo.Lookup_IDTYPE.ID_TypeID INNER JOIN
dbo.Lookup_Ethnicity ON dbo.Resident.EthnicityID = dbo.Lookup_Ethnicity.EthnicityID
WHERE (dbo.Resident.DateOfPlacement >= (GETDATE() - @SinceDaysAgo))
FOR XML PATH('td'), TYPE
) AS NVARCHAR(MAX) ) +

N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail
@recipients=(stricken for privacy),
@subject = 'New Resident(s)',
@body = @tableHTML,
@body_format = 'HTML' ;

END
This sends an email that looks like this:

---------------------------------

(Name stricken for privacy)
DOB: (stricken for privacy)
DOP: 07/18/2008
County: Los Angeles
Agency: Probation
Ethnicity: Black
PDJ: 334060
---------------------------------

(Name stricken for privacy)
DOB: (stricken for privacy)
DOP: 07/15/2008
County: Los Angeles
Agency: Probation
Ethnicity: Black
PDJ: 341049
---------------------------------

(Name stricken for privacy)
DOB: (stricken for privacy)
DOP: 07/18/2008
County: Los Angeles
Agency: Probation
Ethnicity: Black
PDJ: 332905
---------------------------------

As I said, this is satisfactory. However, if you examine the second line of the tr = statements, you'll see that the data label is manually inserted. I'd like to know how to make the labels run down a column of their own, right-justified, so the output would be more like this:

Name: (column division) (Name stricken for privacy)
DOB: (column division) (stricken for privacy)
DOP: (column division) 07/18/2008
County: (column division) Los Angeles
Agency: (column division) Probation
Ethnicity: (column division) Black
PDJ: (column division) 332905

Thanks for any help you can provide!

----------------
-Stephen

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2008-07-28 : 14:23:34

Nobody can help with this? Bummer!

----------------
-Stephen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources