CODEWITHSUNDEEP
sql-serverxml
nlkasyap
nlkasyap

Reputation: 65

Importing XML file into SQL Server 2000 using OPENROWSET

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.

Upvotes: 0

Views: 870

Answers (1)

Jeroen Mostert
Jeroen Mostert

Reputation: 28779

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.

Upvotes: 1

Related Questions