I've found attaching an XML file as an attachment via sp_send_dbmail very troublesome.

I did this with WhoIsActive which is probably a little more complicated than you need.

http://jonmorisissqlblog.blogspot.com/[/url]

Basically the way I figured it out was to create a stored procedure. Within the stored proc I run a select FOR xml path, but return it as NVARCHAR(max). Then I called this stored proc within the @query option of sp_send_dbmail.

EXEC msdb.dbo.sp_send_dbmail

@recipients='',

@profile_name = '[Database Mail Profile]',

@subject = 'Who Is Active',

@body_format = 'TEXT',

@query = 'SET NOCOUNT ON; SET ANSI_WARNINGS OFF; DECLARE @bodyXMLreturn nvarchar(max) EXEC dbo.sp_WhoIsActiveXMLout @bodyXMLreturn OUTPUT select @bodyXMLreturn',

@execute_query_database = '[Database]',

@attach_query_result_as_file = 1,

@query_attachment_filename = 'WhoIsActive.xml',

@query_result_header = 0,

@query_result_width = 32767,

@query_result_separator = '',

@exclude_query_output = 0,

@query_result_no_padding = 0,

@query_no_truncate=1;

HTH,

Jon