将XML文件导入SQL Server而不使用BULK [英] Import XML file into SQL Server without BULK

问题描述

我正在尝试将数据从XML文件导入数据库。我使用 BULK INSERT 制作了一个测试脚本，但是当我在实时数据库上测试时， BULK 被禁用。

I'm trying to import data from a XML file into a database. I've made a test script using BULK INSERT, but when I've tested on the live database, permissions for BULK are disabled.

尝试＃1：

Attempt #1:

BULK INSERT XMLTable FROM 'C:\Radu\test.xml' WITH (ROWTERMINATOR = '>');

所以，我一直在研究如何避免使用 BULK 并找到其他选项，例如 OPENROWSET 和 OPENDATASOURCE 。但不幸的是，这些操作的权利也被撤销。

So, I have continued research to find a way to avoid using BULK and found other options, such as OPENROWSET and OPENDATASOURCE. But unfortunately, rights for these operations are also revoked.

尝试＃2：

Attempt #2:

SELECT * 
FROM   OPENROWSET('MSDASQL',  
'Driver={Microsoft Text Driver (*.xml)};DefaultDir=C:\Radu\test.xml;', 
'SELECT * FROM [test.xml];' )

导致错误：

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online. 

我试过 RECONFIGURE 权限为此，但没有成功。

I've tried to RECONFIGURE the permissions for this, but with no success.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

还导致：

Msg 15247, Level 16, State 1, Procedure sp_configure, Line 94
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Line 2
You do not have permission to run the RECONFIGURE statement.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.
Msg 5812, Level 14, State 1, Line 4
You do not have permission to run the RECONFIGURE statement.

我还在尝试找到一个解决方案，只是为了将信息导入数据库，我不知道需要一种特殊的格式，只是为了获取系统中的数据，虽然理想情况下我想将每个xml行导入我的表中的记录。

I'm still trying to find a solution just to import the information into the database, I don't need a special format, just to get the data in the system, although ideally I would like to import each xml line as a record in my table.

看来我的大多数选择都被切断了，所以我非常感谢任何建议。

It appears that most of my options are cut off, so I would very much appreciate any suggestions.

更新：

我将创建一个存储过程来导入这些数据，所以理想情况下这将是通用的，不需要使用XML信息进行硬编码。

I am going to create a stored procedure to import this data, so ideally this would be generic, no hardcoding with the XML information.

推荐答案

由于您没有批量上传权限，因此您需要一些工具将XML转换为XSLX或XSL，然后打开它并复制所有行并将其粘贴到表中。

Since you dont have bulk upload permissions then you need some tool to convert the XML into XSLX or XSL then open it and copy all the rows and paste it in the table.

或

这仍然使用批量加载组件，尝试它，如果它工作。

This still uses a bulk load component, try it, if it get worked.

http：//支持.microsoft.com / kb / 316005

这篇关于将XML文件导入SQL Server而不使用BULK的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！