Sending FOR XML output via email

[RRinTetons]

I've successfully written a query that selects the data I want. When I add FOR XML AUTO to it it returns a link (in SSMS) that displays the XML output when I click it. I want to send that output via email. Everything I've tried to date end up sending the link, not the XML text.

How do I do that?

-
Richard Ray
DBA, Developer, Data Analyst
Jackson Hole Mountain Resort

 

[RRinTetons]

I've made some progress, but it's still not quite doing what I need.


I have a table valued function called 'GetTramWXData' which takes one parameter, an INT. I can call it as

SELECT * FROM GetTramWXData(1) FOR XML AUTO

and get back the expected results,

<dbo.GetTramWXData DateTimeStamp="2013-07-09T10:39:02.867" TowerName="Top" WindSpeed="4" WindDirection="27" Temperature="45" />
<dbo.GetTramWXData DateTimeStamp="2013-07-09T10:39:02.867" TowerName="Tower 5" WindSpeed="1" WindDirection="331" Temperature="54" />
<dbo.GetTramWXData DateTimeStamp="2013-07-09T10:39:02.867" TowerName="Tower 4" WindSpeed="3" WindDirection="45" Temperature="53" />
<dbo.GetTramWXData DateTimeStamp="2013-07-09T10:39:02.867" TowerName="Tower 3" WindSpeed="3" WindDirection="23" Temperature="54" />
<dbo.GetTramWXData DateTimeStamp="2013-07-09T10:39:02.853" TowerName="Tower 2" WindSpeed="0" WindDirection="328" Temperature="59" />
<dbo.GetTramWXData DateTimeStamp="2013-07-09T10:39:02.743" TowerName="Tower 1" WindSpeed="1" WindDirection="181" Temperature="61" />

I want to email that output. So, I write an sp using DB Mail

CREATE PROCEDURE dbo.SendWXData
AS
	BEGIN
		SET NOCOUNT ON; 
		DECLARE @QueryStr NVARCHAR(MAX)
		SET @QueryStr =  'SELECT * FROM [TramWX].[dbo].[GetTramWXData](5) FOR XML AUTO'
		DECLARE @SubjectStr AS NVARCHAR(64)
		SET @SubjectStr = 'Tram WX' 
		 
		EXEC msdb.dbo.sp_send_dbmail 
		   @recipients = 'xxxx@xxx.com'
		  ,@profile_name = 'JHMR'
		  ,@query = @QueryStr
		  ,@subject = @SubjectStr ;
	END
GO

That executes successfully, but the body of the email contains:

XML_F52E2B61-18A1-11d1-B105-00805F49916B                                                                                                                                                                                                                        
                                                                                                                                                                                                                                                                
  
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--
0x440D4400610074006500540069006D0065005300740061006D007000440954006F007700650072004E0061006D0065004409570069006E00640053007000650065006400440D570069006E00640044006900720065006300740069006F006E00440B540065006D007000650072006100740075007200650044185400720061
006D00570058002E00640062006F002E004700650074005400720061006D005700580044006100740061000106020152F6A10000798AB200020211060054006F00700002034206000000020442590100000205422B000000430106020152F6A10000798AB2000202110E0054006F007700650072002000350002034202000000
02

(1 rows affected)

What do I need to do to get the text output of the XML to appear in the body of the email?

-
Richard Ray
DBA, Developer, Data Analyst
Jackson Hole Mountain Resort

 

[SQLBill]

Try using @body_format = 'html'. As you are running it now, it is converting XML output to text.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[RRinTetons]

I got it to work by converting the xml into nvarchar and passing that as the body.

ALTER PROCEDURE dbo.SendWXData
AS
	BEGIN
		SET NOCOUNT ON; 
--declare an xml var for the first select
		DECLARE @xml XML;
--declare the var to be passed to db_sendmail
		DECLARE @Output NVARCHAR(MAX)
--select the xml into the xml var
		SELECT @xml = (SELECT * FROM [TramWX].[dbo].[GetTramWXData](5) FOR XML AUTO,TYPE)
--and convert the xml var into the nvarchar
		SELECT @Output = CONVERT(VARCHAR(MAX),@xml)
		DECLARE @SubjectStr AS NVARCHAR(64)
		SET @SubjectStr = 'Tram WX' 
		 
		EXEC msdb.dbo.sp_send_dbmail 
		   @recipients = 'rray@xbd.com'
		  ,@profile_name = 'JHMR'
--and use the nvarchar here
		  ,@body = @Output
		  ,@subject = @SubjectStr ;
	END
GO

I'll try the @body_format idea, too, though, since this output is pretty raw.

-
Richard Ray
DBA, Developer, Data Analyst
Jackson Hole Mountain Resort