June 14, 2005 at 7:19 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.
The XML content is enclosed in the double quotes:
"
"
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,
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply