June 14, 2005 at 7:16 am
I'm trying to use OpenXML method to import an XML file to SQL server 2000. Here are some details:
1.The original XML file is saved at c:\Fname2.xml.
2. I created a table in SQL server 2000 called tbleContact with three fields:
ContactID int, Firstname varchar(25), and Email1 varchar(64).
3. Then I wrote a stored procedure using OpenXML:
CREATE PROCEDURE TestOpenXML(
@strXML VARCHAR(2000)
)
AS
DECLARE @XMLDocPointer INT
EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @strXML
BEGIN TRANSACTION
INSERT INTO tblContact(ContactID, FirstName, Email1)
SELECT CONTACTID, FIRSTNAME, EMAIL1
FROM OPENXML(@XMLDocPointer,'/ROWDATA/ROW',2)
WITH (CONTACTID INT '@CONTACTID', FIRSTNAME VARCHAR(25) '@FIRSTNAME', EMAIL1 VARCHAR(64) '@EMAIL1')
COMMIT
EXEC sp_xml_removedocument @XMLDocPointer
RETURN
GO
4. Then, I called TestOpenXML in Query Analyzer using 'c:\Fname2.xml' as parameter for @strXML.
5. The error message I received is as below:
Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 7
XML parsing error: Invalid at the top level of the document.
Any suggestions and advices, please help.
thanks,
June 14, 2005 at 7:30 am
OPENXML is not an import method, it is a parser. You need to pass the XML itself as the @strXML parameter and not its path.
To load the data in initially from an external file, you need to use the SQLXMLBulkLoad object which, IIRC, you get by installing SQLXML which is available free from microsoft.com.
HTH
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply