Solved

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

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

776 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