BCP query export to xml problem

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 query export to xml problem

Author

Topic

MCPietersTP
Starting Member

15 Posts

Posted - 2013-02-20 : 03:23:09

Hi Everyone,

I am trying to export a query to a xml file, but I'm stuck.
this is the code:
declare @Filename varchar(50)
declare @SQLcmd varchar(1500)
set @Filename = 'D:\Quickbooks data bestanden\MCP Test\Supplierdata.xml'
set @SQLcmd = 'bcp ' +
'"Select * from openquery(QODBC, ''select ListID, EditSequence, Name, IsActive, CompanyName, VendorAddressAddr1, VendorAddressAddr2, VendorAddressAddr3, VendorAddressCity, VendorAddressPostalCode, VendorAddressNote,VendorAddressBlockAddr1, VendorAddressBlockAddr2, VendorAddressBlockAddr3, VendorAddressBlockAddr4, Phone, Fax, Email, Contact, NameOnCheck, AccountNumber from vendor'') as Supplierdata
for xml auto, type, elements, root(''Supplierdata'')"' + ' queryout ' + @Filename + ' -w -T -S SERVICES'

execute xp_cmdshell @SQLcmd

The query itself is correct it has xml data as output.

Result of above code is this:
output
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL

Does someone know what I'm doing wrong?

Thank You

Matthijs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-20 : 03:34:26

can you try like this?


declare @Filename varchar(50)
declare @SQLcmd varchar(1500)
set @Filename = 'D:\Quickbooks data bestanden\MCP Test\Supplierdata.xml'
set @SQLcmd = 'bcp ' +
'"Select * from openquery(QODBC, ''select ListID, EditSequence, Name, IsActive, CompanyName, VendorAddressAddr1, VendorAddressAddr2, VendorAddressAddr3, VendorAddressCity, VendorAddressPostalCode, VendorAddressNote,VendorAddressBlockAddr1, VendorAddressBlockAddr2, VendorAddressBlockAddr3, VendorAddressBlockAddr4, Phone, Fax, Email, Contact, NameOnCheck, AccountNumber from vendor'') as Supplierdata
for xml auto, type, elements, root(''Supplierdata'')"' + ' queryout "' + @Filename + '" -w -T -S SERVICES'

execute xp_cmdshell @SQLcmd

I assume SERVICES is your server name and its the default instance

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MCPietersTP
Starting Member

15 Posts

Posted - 2013-02-20 : 03:47:17

Thanks for your reply, but this does not solve the problem, still same result.

What else could be wrong?

Matthijs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-20 : 03:58:25

hope QODBC linked server is created in same server SERVICES

try this too


declare @Filename varchar(50)
declare @SQLcmd varchar(1500)
set @Filename = 'D:\Quickbooks data bestanden\MCP Test\Supplierdata.xml'
set @SQLcmd = 'bcp ' +
'"Select * from openquery(QODBC, ''select ListID, EditSequence, Name, IsActive, CompanyName, VendorAddressAddr1, VendorAddressAddr2, VendorAddressAddr3, VendorAddressCity, VendorAddressPostalCode, VendorAddressNote,VendorAddressBlockAddr1, VendorAddressBlockAddr2, VendorAddressBlockAddr3, VendorAddressBlockAddr4, Phone, Fax, Email, Contact, NameOnCheck, AccountNumber from vendor'') as Supplierdata
for xml auto, type, elements, root(''Supplierdata'')"' + ' queryout "' + @Filename + '" -w -S SERVICES -T'

execute xp_cmdshell @SQLcmd

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MCPietersTP
Starting Member

15 Posts

Posted - 2013-02-20 : 03:58:39

The problem is with authentication, because when I connect to sql server with windows authentication, it works. But I want to use sql server authentication with user 'Pietje' and password 'oops' for example, How do i change the sql code?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-20 : 04:23:50

your bcp command is also trying to use windows authentication itself. Then wats the issue?

Anyways ,if you want to use sql account change like


declare @Filename varchar(50)
declare @SQLcmd varchar(1500)
set @Filename = 'D:\Quickbooks data bestanden\MCP Test\Supplierdata.xml'
set @SQLcmd = 'bcp ' +
'"Select * from openquery(QODBC, ''select ListID, EditSequence, Name, IsActive, CompanyName, VendorAddressAddr1, VendorAddressAddr2, VendorAddressAddr3, VendorAddressCity, VendorAddressPostalCode, VendorAddressNote,VendorAddressBlockAddr1, VendorAddressBlockAddr2, VendorAddressBlockAddr3, VendorAddressBlockAddr4, Phone, Fax, Email, Contact, NameOnCheck, AccountNumber from vendor'') as Supplierdata
for xml auto, type, elements, root(''Supplierdata'')"' + ' queryout "' + @Filename + '" -w -S SERVICES -U username -P password'

execute xp_cmdshell @SQLcmd

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MCPietersTP
Starting Member

15 Posts

Posted - 2013-02-21 : 06:12:13

I still have problems exporting query to xml.
When I execute following code it works:
declare @Filename varchar(50)
declare @SQLcmd varchar(1500)
set @Filename = 'D:\Quickbooks data bestanden\MCP Test\Supplierdata.xml'
set @SQLcmd = 'bcp ' +
'"Select 27378 as supdata for xml raw, type, elements, root(''Supplierdata'')"' + ' queryout "' + @Filename + '" -w -S SERVICES -T'
execute xp_cmdshell @SQLcmd

But when I exexute this code, i still get above result(see earlier message):
declare @Filename varchar(50)
declare @SQLcmd varchar(1500)
set @Filename = 'D:\Quickbooks data bestanden\MCP Test\Supplierdata.xml'
set @SQLcmd = 'bcp ' +
'"Select * from openquery(QODBC, ''select ListID, EditSequence, Name, IsActive, CompanyName, VendorAddressAddr1, VendorAddressAddr2, VendorAddressAddr3, VendorAddressCity, VendorAddressPostalCode, VendorAddressNote,VendorAddressBlockAddr1, VendorAddressBlockAddr2, VendorAddressBlockAddr3, VendorAddressBlockAddr4, Phone, Fax, Email, Contact, NameOnCheck, AccountNumber from vendor'') as Supplierdata
for xml auto, type, elements, root(''Supplierdata'')"' + ' queryout "' + @Filename + '" -w -S SERVICES -T'
execute xp_cmdshell @SQLcmd

the query itself has xml as a result...

does someone know what's wrong?

MCPietersTP
Starting Member

15 Posts

Posted - 2013-02-21 : 07:21:48

there is something wrong with the 'for xml' part, because when i leave it out the bcp operation succeeds.

But what?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-21 : 22:49:00

can you use for xml path instead and try?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MCPietersTP
Starting Member

15 Posts

Posted - 2013-02-25 : 03:34:45

No, that does not help either???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-25 : 04:17:45

first run for xml path alone and see if it gives you intended xml result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources