Solved

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

Posted on 2011-03-14
3
452 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 39

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 500 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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

912 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

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now