CODEWITHSUNDEEP
sqlt-sqlazure
dg90
dg90

Reputation: 1263

XML export via BCP Bulk Export

I got a table tblScan with some varbinary data. I want to export this table to an XML-file so I can insert this in SQL azure with BCP Bulk insert. ( BTW is it even possible to convert varbinary data to XML ?? )

I'm doing:

DECLARE @String varchar(8000)
SET @String='bcp "SELECT * FROM dbo.tblScan FOR XML RAW, XMLSCHEMA" queryout C:\Users\DAAGEU\Desktop\tblScanOutput.xml -c -T'
EXEC xp_cmdshell @String

But I got this output error:

Output error

Are there any other solutions to do this?

Upvotes: 3

Views: 6726

Answers (1)

astaykov
astaykov

Reputation: 30903

It is actually a problem with VarBinary and XML (in SQL 2008 R2):

FOR XML EXPLICIT and RAW modes currently do not support addressing binary data as URLs in column SomeComlumn. Remove the column, or use the BINARY BASE64 mode, or create the URL directly using the 'dbobject/TABLE[@PK1="V1"]/@COLUMN' syntax.

Quick searching showed this post.

Is there particluar reason that you want to export in XML? I have been using bcp using the default binary output and there are no issues with VarBinary fields. Binary files are quite smaller then the XML files. And if you don't want to do any manipulation between export and import, I suggest that you use binary files.

Try changing your query to:

SET @String='bcp "SELECT * FROM dbo.tblScan FOR XML RAW, BINARY BASE64" queryout C:\Users\DAAGEU\Desktop\tblScanOutput.xml -c -T'

However, as seen by the logs, your current issue is the login. You are using trusted connection, but do you know which identity is running the bcp process, when it is spawned by the SQL Server engine? It would most probably the SQL Server identity, which I guess does not have access to the database/table (and most probaly is Local System or Network Service). If you still want to make this using SQL query, and not a batch file (.bat or .cmd) I suggest that you use explicit login credentials (SQL Server auth) and not trusted connection.

Upvotes: 4

Related Questions