When you use msdb.dbo.sp_send_dbmail
to send a query result as an attachment, you'll have to provide the @query_result_width
parameter which is an integer between 10 and 32767.
Now, If you use For XML Path('Row'), Root('Data'), Type
in your most-outer Select query, you may get the send_dbmail to send an xml attachment for you which is really easy to open in Microsoft Excel (just 2 or 3 clicks and it's a nice excel table)
But, if your xml file exceeds the 32767 bytes, the send_dbmail WILL insert a "line-break" at 32767th column of the first line of xml file, and it does this UNCONDITIONALLY! It means it doesn't care what's written there; it just breaks the line and therefore WILL corrupt your xml data...
I've been wandering the internet for more than 5 hours with no positive result on how should I prevent sp_send_dbmail from injecting that nasty line-break.
The only solution I came across was injecting the line-break where I have the control and it's not after 32767th column. A query like the following will do the trick, but it's extra work and not clean...
Select Replace(
Cast(
(
Select
*
From Table
For XML Path('Row'), Root('Data'), Type
)
As NVarChar(Max))
, N'/>', N'/><!--This is the controlled line-break. Remove this comment-->
')
Anybody has any better idea? Am I missing something here?
I use SQL Server 2008 Enterprise (x64)