?
Solved

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

Posted on 2011-03-14
3
Medium Priority
?
489 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
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

850 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