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

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

Select allOpen in new window