BCP for .xml Export Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

BCP for .xml Export Error

Author

Topic

nathansi
Starting Member

4 Posts

Posted - 2013-11-20 : 01:06:00

Hi All,

I'm trying to export some tables from SQL Server 2012 into .xml format in a local drive and have run into a problem when reading the file in Excel for example.

The code I'm currently using is:

DECLARE @FileName varchar(50),
@bcpCommand varchar(8000)

SET @FileName = 'D:\SQLextracts\dbo.vwPOLICIES_Test'+'.xml'

SET @bcpCommand = 'bcp "SELECT pvTransactionID, pvPolicyID, pvParentPolicyID, pvProductTypeName, pvClientID, pvBindRequestDate, pvEffectiveDate, pvBasePremiumAmt, pvCommissionAmount, pvAdminFee, pvStampDutyAmount, pvFireServicesLevy, pvBrokerCommission, pvBroker, pvProviderPolicyRef, pvTermExpiryDate FROM UnderwriterCENTRAL_Test.dbo.vwPOLICIES FOR XML AUTO, ROOT(''dbo.vwPOLICIES''), ELEMENTS" QUERYOUT "'

SET @bcpCommand = @bcpCommand + @FileName + '" -T -c -r -t'
PRINT @bcpCommand
EXEC master..xp_cmdshell @bcpCommand , No_output
go

I've gone through each of the fields exported and the column pvInsuredName is what's causing the error. If I include column pvInsuredName in the script I end up with the Excel error message 'invalid file reference. the path to the file is invalid, or one or more of the referenced schemas could not be found' .

I'm guessing there's an issue with some of the characters contained within the column pvInsuredName causing the error however I'm sure how to fix it.

Any help is greatly appreciated.

Thanks and regards,
Nathan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-20 : 02:07:01

i think it may have some characters which is causing xml to get broken. can you post some data from it for us to see what might be causing this.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

nathansi
Starting Member

4 Posts

Posted - 2013-11-20 : 17:13:58

Hi,

Sure, some of the examples are as below:

* 13 Anzac Street, Sarina, CTS TBA
* 64 Kate St, Morningside CTS TBA
* Hilton , elan, dolphin,
* Otter Holdings Australia Pty Ltd,

I've searched for the ones where a comma is involved as I'm thinking that there is an issue with the delimiter I was using. Correct me if I'm wrong.

Thanks for the quick response.

Regards,
Nathan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-20 : 17:28:01

If it's a delimiter issue, have you tried a different delimiter (one that doesn't appear in your data)? How about -t"|"?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

nathansi
Starting Member

4 Posts

Posted - 2014-02-11 : 19:52:02

I'm not too familiar with xml's, but what types of characters could break it?

I've also tried using the field delimiter -t~ of which character ~ is definitely nowhere in the system. I'm also guessing the row delimiter -r is fine as well?

Is the syntax done incorrectly?

Regards,
Nathan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources