Using bcp to extract xml result, but output file has newline characters

I have this piece of T-SQL

DECLARE @FileName VARCHAR(100)
DECLARE @SQLCmd   VARCHAR(500)

SELECT  @FileName = '\\ftp.myplace.com\FtpFiles\Extract.xml'

SELECT  @SQLCmd = 'bcp ' +
                  '"exec myDB.dbo.myXMLstoredProcedure"' +
                  ' queryout '  +
                  @FileName +
                  ' -w -T -S ' + @@SERVERNAME

EXECUTE master..xp_cmdshell @SQLCmd

The stored procedure "myXMLstoredProcedure" reads some tables and uses for xml path to create an xml string.

The problem is that when bcp exports that string to the destination file, it inserts newline characters (CR LF) every 2033 characters and this prevents my xml file to be parsed afterwards.

Is there a way to prevent bcp from inserting newlines that are not there in the original data?

I've tried the option -a 65535, but it did not solve it. The options -n and -N did prevent the newlines, but then all the characters were separated by a null.

Solution

Add the TYPE clause to your FOR XML statement, and use the -w switch of bcp when exporting XML. You would also make things easier on yourself by executing a stored proc in the queryout rather than a SQL statement, eg

USE tempdb
GO

IF OBJECT_ID('dbo.usp_myProc') IS NOT NULL DROP PROC dbo.usp_myProc
GO

CREATE PROC dbo.usp_myProc AS

SET NOCOUNT ON

SELECT yourColumns
FROM yourTable
FOR XML AUTO, ROOT('Courses'), TYPE

RETURN
GO

EXEC xp_cmdshell 'bcp "EXEC tempdb.dbo.usp_myProc" queryout "c:\temp\Courses.xml" -S (local) -T -w'