asked
output stored procedure to xml through dbmail
Hi all, I am trying to take the output of this stored procedure in XML format with tags like etc... and send it to a user through databasemail however in testing, the output comes out correctly as a hyper link in the results window of management studio but the email displays something like this: 0x440372006F007700440A4A00300033005F0045004D0050005F0049004400440D4A00300033005F004A004F0042005F005400490054004C004500440A4A00300033005F004F0046004600490043004500440B4A00300033005F00540045004C005F00450058005400440C4A00300033005F004400490052005F004400490041004C00440A4A00300033005F004F004E004C0049004E004500440D4A00300033005F0044004500500054005F004C004F004E004700440C4A00300033005F004200550049004C00440049004E004700440D4A00300033005F005300490047004E0041005400550052004500010102000978006D006C006E0073003A0078007300690011520068007400740070003A002F002F007700770077002E00770033002E006F00720067002F0032003000300031002F0058004D004C0053006300680065006D0061002D0069006E007300740061006E0063006500410102410310E404000007003030303030303681020103410310E404000016004D6173746572206F662074686520556E69766572736581030104410310E404000004003130323281040105410310E404000016002833313229203432372D323733372045787420383030810501060200077800730069003A006E0069006C000601430107410310E404000001004E81070108410310E40400001E00496E666F726D6174696F6E20546563686E6F6C677920536572766963657381080109410310E40400000800506C796D6F7574688109010A410310E404000001004E810A8101010102000978006D006C006E0073003A0078007300690011520068007400740070003A002F002F007700770077002E00770033002E006F00720067002F0032003000300031002F0058004D004C0053006300680065006D0061002D0069006E007300740061006E0063006500410102410310E404000007003030303030343081020103410310E40400000E004D617374657220416E616C79737481030104410310E4040000030037333381040105410310E404000016002833313229203432372D323733372045787420373931810501060200077800730069003A006E0069006C000601430107410310E404000001004E81070108410310E40400001E00496E666F726D6174696F6E20546563686E6F6C6779205365727669636573810801090200077800730069003A006E0069006C00060143010A410310E4040000010059810A8101 (1 rows affected) Here is the stored procedure: USE [coll18_test02] GO /****** Object: StoredProcedure [dbo].[Populate_Emp_Directory] Script Date: 07/17/2013 09:25:08 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Populate_Emp_Directory] AS BEGIN declare @query nvarchar(max) set @query = N'SELECT [J03_EMP_ID] as [ID]' + N',[J03_JOB_TITLE] as [JOB_TITLE]' + N',[J03_OFFICE] as [OFFICE]' + N',[J03_TEL_EXT] as [EXT]' + N',[J03_DIR_DIAL] as [DIRECT_DIAL]' + N',[J03_ONLINE] as [DISPLAY_ONLINE_FLAG]' + N',[J03_DEPT_LONG] as [DEPARTMENT]' + N',[J03_BUILDING] as [BUILDING]' + N',[J03_SIGNATURE] as [SIGNATURE]' + N'FROM [coll18_test02].[dbo].[J03_EMP_DIR]' + N'FOR XML PATH, ELEMENTS XSINIL' exec msdb.dbo.sp_send_dbmail @
recipients='muk@jmls.edu', @query=@query, @execute_query_database='coll18_test02', @query_no_truncate=1, @exclude_query_output=1, @query_result_width=32767, @profile_name = 'test' END Anyone see what is wrong here???? Is there another way to do this? I need to send this xml file in this format with as least manual intervention as possible so I thought I could make this stored procedure a weekly job to just create the xml file and send the email. Help!!!!!!
That looks like the varbinary representation of the xml. Not sure why you are getting that as when I replicate your query on my environment, I do get XML in the email. You could try explicitly casting the XML to varchar(max) set @query = N' select convert(varchar(max), (select( SELECT [J03_EMP_ID] as [ID] ,[J03_JOB_TITLE] as [JOB_TITLE] ,[J03_OFFICE] as [OFFICE] ,[J03_TEL_EXT] as [EXT] ,[J03_DIR_DIAL] as [DIRECT_DIAL] ,[J03_ONLINE] as [DISPLAY_ONLINE_FLAG] ,[J03_DEPT_LONG] as [DEPARTMENT] ,[J03_BUILDING] as [BUILDING] ,[J03_SIGNATURE] as [SIGNATURE] FROM [coll18_test02].[dbo].[J03_EMP_DIR] FOR XML PATH, ELEMENTS XSINIL ))) as XMLforEmail'
@Kev Riley or anyone else, does anyone know how I can insert line breaks between the tags on this?
14 People are following this question.
