asked
stored procedure to xml
Hello all, I wrote a stored procedure to query a staff directory table and put it in "xml" format. I have another query that reads this stored procedure and sends me a daily email of an xml file populated with this information. The problem is between each record there is a ton of spaces! I am talking about at least 20-25 lines of space between each person's record starting with and ending with . Here is the stored procedure that gets the information and formats it to look like xml. USE [coll18_production] GO /****** Object: StoredProcedure [dbo].[GetEmpDir] Script Date: 08/21/2013 10:56:37 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Margarita Uk -- Create date: 8/21/13 -- Description: converts employee directory to xml for website. -- ============================================= ALTER PROCEDURE [dbo].[GetEmpDir] -- Add the parameters for the stored procedure here AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT ' '+ CHAR(13) + CHAR(10) +' ' + [J03_EMP_ID] + ' ' + char(13) + CHAR(10) + ' ' + IsNull(PERSON.FIRST_NAME,'') + ' ' + CHAR(13) + CHAR(10) + ' ' + IsNull( PERSON.MIDDLE_NAME,'') + ' ' + CHAR(13) + CHAR(10) + ' ' + ISNULL(PERSON.LAST_NAME,'') + ' ' + CHAR(13) + CHAR(10) + ' ' + ISNULL(PERSON.LAST_NAME,'') + '_' + IsNull(PERSON.FIRST_NAME,'') + ' ' + CHAR(13) + CHAR(10) + ' ' + ISNULL(PERSON.SOURCE,'') + '' + CHAR(13) + CHAR(10) + ' ' + ISNULL(PERSON.NICKNAME,'') + ' ' + CHAR(13) + CHAR(10) + ' ' + REPLACE(ISNULL(J03_JOB_TITLE,''), '&','&') + ' ' + CHAR(13) + CHAR(10) + ' ' + ISNULL(J03_OFFICE,'') + ' ' + CHAR(13) + CHAR(10) + ' ' + ISNULL(J03_TEL_EXT,'') + ' ' + CHAR(13) + CHAR(10) + ' ' + ISNULL(J03_DIR_DIAL,'') + ' ' + CHAR(13) + CHAR(10) + ' ' + ISNULL(J03_ONLINE,'') + ' ' + CHAR(13) + CHAR(10) + ' ' + REPLACE(ISNULL(J03_DEPT_LONG,''),'&','&') + ' ' + CHAR(13) + CHAR(10) + ' ' + ISNULL(J03_BUILDING,'') + ' '+ char(13) + char(10) +' ' FROM J03_EMP_DIR LEFT JOIN PERSON ON J03_EMP_DIR.J03_EMP_ID = PERSON.ID where PERSON.SOURCE IN ('FAC','STA') AND (PERSON.PERSON_STATUS = 'A' or PERSON.PERSON_STATUS is null) order by PERSON.LAST_NAME, PERSON.FIRST_NAME END And here is the stored procedure that emails the info: USE [coll18_production] GO /****** Object: StoredProcedure [dbo].[Populate_Emp_Directory] Script Date: 08/21/2013 11:35:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Populate_Emp_Directory] AS BEGIN exec msdb.dbo.sp_send_dbmail --@recipients
='RSvea@jmls.edu', @
recipients='muk@jmls.edu', @query = 'EXEC dbo.GetEmpDir', @body = 'Is this okay?', @execute_query_database='coll18_production', @query_no_truncate=1, @exclude_query_output=1, @query_result_width=32767, @profile_name = 'test', @attach_query_result_as_file=1, @query_attachment_filename='directory.xml' END Basically when I get the xml file in my email and pull it down to a notepad, you see all the spaces. Is there any way to omit these spaces? Thanks, Margarita
Try replacing the `@query_no_truncate` parameter with `@query_result_no_padding=1` `@query_result_no_padding` will also override `@query_result_width` so you can probably drop that parameter, too. Here is the Books Online documentation snippet for this: ---------- [ @query_result_no_padding ] @query_result_no_padding The type is bit. The default is 0. When you set to 1, the query results are not padded, possibly reducing the file size. If you set @query_result_no_padding to 1 and you set the @query_result_width parameter, the @query_result_no_padding parameter overwrites the @query_result_width parameter. In this case no error occurs. If you set the @query_result_no_padding to 1 and you set the @query_no_truncate parameter, an error is raised. ---------- [
http://technet.microsoft.com/en-us/library/ms190307.aspx][1] [1]:
http://technet.microsoft.com/en-us/library/ms190307.aspx
15 People are following this question.
