Import XML file into SQL Server without 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.

Attempt #1:

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

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.

Attempt #2:

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

resulted in an error:

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.

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;

also resulted in:

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.

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.

UPDATE:

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

回答1:

For BULK INSERT operation, you need administrator privileges for that database. You can take XML data in XML variable and insert into table as mentioned in this link :

Import 'xml' into Sql Server

回答2:

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://support.microsoft.com/kb/316005