?
Solved

SQL 2005 TO XML FILE USING BCP WITH NO CR/LF

Posted on 2011-03-14
3
Medium Priority
?
473 Views
Last Modified: 2012-07-25
Hi,

I have the following SQL script to create an XML file.  It works fine except that it puts a carriage return at the end of the file.  The XML import program (which I have no control over) that picks up the file, does not like that carriage return and errors out.  Somehow I need to either eliminate all carriage returns in the file or just eliminate that single carriage return at the end of the file.  Any ideas how I can make my current script do this and/or a better way to do it?  

 
DECLARE
 @wh_id varchar(2),
 @order_number varchar(12),
 @FileName varchar(100),
 @bcpCommand varchar(2000)
SET @order_number = '79746-00'
SET @wh_id = 'DL'     
SET @FileName = '\\DC-p61\DC_share\' + @wh_id + '_' + @order_number + '_XML.xml'
SET @bcpCommand = 'bcp "SELECT XML_data from ZDATA.Costing.dbo.XML_OUTPUT order by XML_sequence" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U sa -P password -c'

delete ZDATA.Costing.dbo.XML_OUTPUT where XML_type in ('D','S')

Insert into ZDATA.Costing.dbo.XML_OUTPUT
SELECT '<DomesticExport>' +[DomesticExport]+ '</DomesticExport><RailTruck>' +[RailTruck]+ '</RailTruck><TruckLicense>' +
ISNULL([TruckLicense],'') + '</TruckLicense><InOutState>' + [InOutState] + '</InOutState>', 'S', 5
FROM [ZMS-DB].AAD.dbo.t_coc_ship_main
where wh_id = @wh_id and order_number = @order_number

Insert into ZDATA.Costing.dbo.XML_OUTPUT
select '<Detail><CropYear>'+ CropYear +'</CropYear><Organic>' + Organic +'</Organic><Storage>' + Storage + '</Storage><Commodity>' + Commodity + '</Commodity><Variety>' +
Variety + '</Variety><ContainerType>' + ContainerType + '</ContainerType><ContentIntCount>' + Cast(ContentIntCount as varchar(10)) + '</ContentIntCount><ContentLbs>' + CAST(ContentLbs as varchar(10)) +
'</ContentLbs><NoShipped>' + CAST(NoShipped as varchar(10))+ '</NoShipped><Grade>' + Grade + '</Grade><order_number>'+order_number+'</order_number><PreCOC>' +PreCOC+ '</PreCOC></Detail>','D',7
From [ZMS-DB].AAD.dbo.t_coc_ship_detail
where wh_id = @wh_id and order_number = @order_number

Insert into ZDATA.Costing.dbo.XML_OUTPUT
Select '<ComplianceStmt>' + [ComplianceStmt] + '</ComplianceStmt><Destination><FinalWarehouse>' + [FinalWarehouse] +
'</FinalWarehouse><NonProfitBusiness>' + [NonProfitBusiness] + '</NonProfitBusiness><CompanyName>' + [CompanyName] +
'</CompanyName><City>' + [City] + '</City><State>' + [State] + '</State><ZipCode>' + [ZipCode] + '</ZipCode><Country>' +
[Country] + '</Country></Destination>','S',9
FROM [ZMS-DB].AAD.dbo.t_coc_ship_main
where wh_id = @wh_id and order_number = @order_number

EXEC master..xp_cmdshell @bcpCommand

Open in new window


0
Comment
Question by:zirklefruit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 35132717
You should be able to use -r switch = row terminator - just check BCP details at
http://msdn.microsoft.com/en-us/library/ms162802.aspx
0
 

Author Comment

by:zirklefruit
ID: 35132811
Already tried that.  There is no way to specifiy a NULL or 'No row terminator'.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1500 total points
ID: 35143538
If the Xml is larger than 2K, you probably cannot use BCP and will have to find some other tool.
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question