通过BCP批量导出进行XML导出 [英] XML export via BCP Bulk Export

问题描述

我有一个包含一些varbinary数据的表tblScan.我想将此表导出为XML文件，因此可以使用BCP批量插入将其插入SQL Azure中.(顺便说一句，甚至有可能将varbinary数据转换为XML ?? )

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 ?? )

我正在做

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:

还有其他解决方案吗?

推荐答案

VarBinary和XML(在SQL 2008 R2中)实际上是一个问题:

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

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

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.

快速搜索显示此帖子.

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

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.

尝试将查询更改为:

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

但是，从日志中可以看到，您当前的问题是登录.您正在使用受信任的连接，但是当SQL Server引擎生成bcp进程时，您知道哪个身份正在运行bcp进程吗?它很可能是SQL Server身份，我想它不能访问数据库/表(并且最可能是本地系统或网络服务).如果仍要使用SQL查询而不是批处理文件(.bat或.cmd)来进行此操作，建议您使用显式登录凭据(SQL Server身份验证)而不是受信任的连接.

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.

这篇关于通过BCP批量导出进行XML导出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！