SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 BCP export to XML issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Cooper_32
Starting Member

United Kingdom
3 Posts

Posted - 05/15/2014 :  10:41:50  Show Profile  Reply with Quote
Hi

I am having some problems exporting an XML file via BCP.
I have created a stored procedure that creates the XML file, complete with the version declaration <?xml version="1.0" encoding="utf-8" ?>.

If I run the procedure manually through sql management studio it correctly creates the file complete with the declaration and saves it to my local path.

However when I run it through BCP, it strips off the declaration.
Can anyone assist with the correct BCP options, or suggest an alternative.


BCP code below
**************

Set @BcpCommand = 'BCP ' + @BcpSqlQuery + ' queryout "' + @FileLocation+'\'+@FileName + '" -w -S'+@InstanceName+' -Usa -Psa'
exec xp_cmdshell @BcpCommand, 'NO_OUTPUT'

Edited by - tkizer on 05/15/2014 15:01:51

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 05/15/2014 :  12:20:36  Show Profile  Visit tkizer's Homepage  Reply with Quote
Show us the output of this: PRINT @BcpCommand

Maybe you need to add -c and -r\r\n?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Cooper_32
Starting Member

United Kingdom
3 Posts

Posted - 05/15/2014 :  14:20:30  Show Profile  Reply with Quote
Hi

Here is the printout, as requested

BCP exec.Benson.dbo.UDEF_SP_XML_DIM_ORDERS_OUT queryout "C:\Upload\OrderUpload.xml" -w -x -SDM6S9W1\SQL2008R2 -Usa -Psa

I should add however, that I posted this to the wrong forum, as it's
SQL 2008 not 2000
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 05/15/2014 :  15:02:35  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by Cooper_32


I should add however, that I posted this to the wrong forum, as it's
SQL 2008 not 2000



I moved the topic to a 2008 forum.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 05/15/2014 :  15:03:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
Did you try adding the -c parameter?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Cooper_32
Starting Member

United Kingdom
3 Posts

Posted - 05/15/2014 :  16:38:48  Show Profile  Reply with Quote
I did but it hasn't made any difference.
Do the options have to be in a set order?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 05/15/2014 :  16:59:00  Show Profile  Visit tkizer's Homepage  Reply with Quote
After the file path/name, no.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000