通过BCP批量导出导出XML(XML export via BCP Bulk Export)

通过BCP批量导出导出XML(XML export via BCP Bulk Export)

我有一个带有一些varbinary数据的表tblScan。 我想将此表导出为XML文件，因此我可以使用BCP Bulk insert在SQL azure中插入它。 （ BTW甚至可以将varbinary数据转换为XML ?? ）

我正在做：

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

但是我得到了这个输出错误：

有没有其他解决方案可以做到这一点？

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:

Are there any other solutions to do this?

最满意答案

它实际上是VarBinary和XML的问题（在SQL 2008 R2中）：

FOR XML EXPLICIT和RAW模式目前不支持将二进制数据作为URL SomeComlumn中的URL进行寻址。 删除列，或使用BINARY BASE64模式，或使用'dbobject / TABLE [@ PK1 =“V1”] / @ COLUMN'语法直接创建URL。

快速搜索显示这篇文章 。

是否有特殊原因要导出XML？ 我一直在使用默认二进制输出的bcp，并且VarBinary字段没有问题。 二进制文件比XML文件小很多。 如果您不想在导出和导入之间进行任何操作，我建议您使用二进制文件。

尝试将您的查询更改为：

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

但是，正如日志所示，您当前的问题是登录。 您正在使用可信连接，但是当SQL Server引擎生成时，您知道哪个标识正在运行bcp进程吗？ 它很可能是SQL Server身份，我猜这个身份无法访问数据库/表（最常见的是本地系统或网络服务）。 如果您仍想使用SQL查询而不是批处理文件（.bat或.cmd），我建议您使用显式登录凭据（SQL Server身份验证）而不是可信连接。

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.