July 9, 2013 at 10:58 am
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?
July 9, 2013 at 12:28 pm
I thought I saw the solution to your problem as one of the XML data type being casted improperly when the result was returned and therefore being incorrectly populated in the body of the email. I was wrong. I have tried several different conversions of it and just can't seem to put my finger on the problem. I'm getting the same thing as you are. I've looked at the source of the email received and it really is being delivered this way; it's not a rendering problem in the email client.
Hopefully this post will help someone who knows more about emailing XML than I do.
July 9, 2013 at 12:49 pm
i think it has to do with FOR XML returns a stream of xml, and not an object we typically expect.
this is what i used for the proof of concempt on my side:
DECLARE @x xml;
SET @x = (
top 5 object_id,name
FROM sys.tables
FOR XML AUTO,TYPE)
--proof of concept.
select convert(varchar(max),@x)
I think doing it this way, where you stuff the FOR XML results into an xml object, and then munge it into a varchar(max) will work for you instead:
CREATE PROCEDURE dbo.SendWXData
AS
BEGIN
SET NOCOUNT ON;
DECLARE @QueryStr NVARCHAR(MAX)
DECLARE @x xml;
SET @x = (
SELECT * FROM [TramWX].[dbo].[GetTramWXData](5)
FOR XML AUTO,TYPE)
--proof of concept.
select convert(varchar(max),@x)
DECLARE @SubjectStr AS NVARCHAR(64)
SET @SubjectStr = 'Tram WX'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'xxxx@xxx.com'
,@body=@x
,@subject = @SubjectStr ;
END
Lowell
July 9, 2013 at 1:19 pm
That may just be it. I tried it having the sp_send_dbmail procedure fire the query with several different CONVERT functions using the XML in a variable and a table, all to no avail. It put garbage into the body of the email even though the data was CONVERTed. Reading the XML and converting it into a variable works.
Thanks, Lowell. I just learned something I can apply - Don't try to email XML without first converting it to character data. Inline conversion doesn't seem to work.
July 9, 2013 at 4:00 pm
Just one other change to keep db_sendmail happy:
ALTER PROCEDURE dbo.SendWXData
AS
BEGIN
SET NOCOUNT ON;
DECLARE @xml XML;
--explicitly declare the var to be passed to db_sendmail
DECLARE @Output NVARCHAR(MAX)
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 had to CONVERT the xml var into another var typed explicitly as NVARCHAR(MAX) or else I got this error back from db_sendmail
Msg 257, Level 16, State 3, Procedure sp_send_dbmail, Line 0
Implicit conversion from data type xml to nvarchar(max) is not allowed. Use the CONVERT function to run this query.
And now it works!
Thanks much!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply