muk avatar image
muk asked

stored procedure help - xml

I have a stored procedure written as such: USE [coll18_test02] GO /****** Object: StoredProcedure [dbo].[Populate_Emp_Directory] Script Date: 08/15/2013 10:20:44 ******/ 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 convert(varchar(max), (select( SELECT [J03_EMP_ID] as [ID] ,PERSON.FIRST_NAME , Char(13) + Char(10) ,PERSON.MIDDLE_NAME , Char(13) + Char(10) ,PERSON.LAST_NAME , Char(13) + Char(10) ,PERSON.LAST_NAME + ''_'' + PERSON.FIRST_NAME AS LAST_FIRST ,PERSON.NICKNAME ,[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] ,PERSON.SOURCE FROM [coll18_test02].[dbo].[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 LAST_FIRST FOR XML PATH, ELEMENTS XSINIL ))) as XMLforEmail' exec msdb.dbo.sp_send_dbmail --@recipients ='', --@recipients ='', @ recipients='', @query=@query, @execute_query_database='coll18_test02', @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 It emails a file with the output like this: 0007345 Amy Davis Davis_Amy Sharepoint Administrator 7 (312) 427-2737 Ext 722 N Information Technolgy Services N STA 0000006 Jeffrey R. Rothschild Rothschild_Jeffrey Master of the Universe 1022 (312) 427-2737 Ext 800 N Information Technolgy Services Plymouth N STA 0000040 Lynne Test Tagatz Tagatz_Lynne Master Analyst 733 (312) 427-2737 Ext 791 N Information Technolgy Services Y STA 0071265 Margarita Uk Uk_Margarita Database Administrator 733 (312) 427-2737 Ext 804 N Information Technolgy Services Plymouth N STA (1 rows affected) Is there anyway to do the following things: 1. insert line breaks between tags? 2. add to the top: 3. below that add this opening tag? and add to the bottom 4. to 5. remove from the bottom: 1 rows affected) 6. If there is no content for a tag, leave out xsi:nil="true" Any advice would be greatly appreciated!!!!!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

KenJ avatar image
KenJ answered
I think you're left with hand-crafting the query to generate XML. I don't think XML PATH lets you do what you're after Here's what part your query might look like, stitching together your own XML. Empty tags are just empty tags and each row gets a new line: SELECT '' + [J03_EMP_ID] + '' + IsNull(PERSON.FIRST_NAME,'') + ' ' + IsNull( PERSON.MIDDLE_NAME,'') + ' ' + etc... + ' ' Finally, just use SET NOCOUNT ON before the query to leave out the rows affected
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Glad to help! You can "accept" the answer so others know this was what worked for you. To get your first line in, stick it immediately above the existing query and use UNION ALL to combine the results: SELECT 'xml version=''1.0'' encoding=''UTF-8'' standalone=''yes''' UNION ALL
1 Like 1 ·
Thank you soooooooooo much!!! @KenJ that did the trick. The only last question is do you know any way I could just add this to the top of the file one single time, not on every row: xml version='1.0' encoding='UTF-8' standalone='yes' Thanks again, I love askSSC :)
0 Likes 0 ·
@KenJ you are a beautiful human being!!!! Thank you so much!!!!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.