CODEWITHSUNDEEP
sql-serverxmlstored-proceduresbcp
ppp
ppp

Reputation: 5

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.

Upvotes: 0

Views: 4328

Answers (2)

Kent Prusas
Kent Prusas

Reputation: 31

I had a similar problem exporting XML and wanting to preserve CDATA tags but getting the new line every 2033 characters.

I added the -r option to the bcp output.

-r defines the newline character in the bcp output so by adding -r without any following character, there are no new lines.

The TYPE option on the FOR XML clause was not an option as it removed the CDATA tags even though it stopped new lines occuring

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

Upvotes: 3

Vinayak
Vinayak

Reputation: 46

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'

Upvotes: 3

Related Questions