Importing XML file into SQL Server 2000 using OPENROWSET

Question

I'm trying to import an XML fie into SQL Server 2000 (SP2) table. I tried below query and it's giving syntax error near the word BULK. Not entirely sure if BULK and SINGLE_BLOB work in SQL Server 2000 SP2.

SELECT * FROM OPENROWSET(BULK N'E:\temp\PersonData.xml', SINGLE_BLOB) AS x

Also tried below query....

SELECT * FROM   OPENROWSET('MSDASQL',  
'Driver={Microsoft Text Driver (*.xml)};DefaultDir=E:\temp\PersonData.xml;', 
'SELECT * FROM [PersonData.xml];' )

....and it gave this error:

[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].

What am I doing wrong here? or Is it a SQL server 2000 issue?. My goal is to import an XML file into SQL Server using a stored procedure. Can't upgrade the server nor can I use a third party tool. Within these boundaries, please suggest me a way to achieve this. Thanks in advance.

Community · Accepted Answer · 2017-05-23 12:11:42Z

1

OPENROWSET(BULK ...) was introduced in SQL Server 2005, so you can't use it.

OPENROWSET('MSDASQL', ...) is notorious for giving unhelpful error messages. See Error: "OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found ..." for a related question; this suggests that the file name should not be part of the DefaultDir parameter (so DefaultDir=E:\temp).

If all else fails, you could use BULK INSERT with bogus values for the field and row terminators to read the entire file in a single NTEXT column; you can then use sp_xml_preparedocument and OPENXML to read it.

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered Oct 20, 2014 at 11:02

Jeroen Mostert

29k2 gold badges60 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nlkasyap Over a year ago

Nice call about using BULK INSERT. Worked. Thanks a lot

Collectives™ on Stack Overflow

Importing XML file into SQL Server 2000 using OPENROWSET

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related