Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument - Urgent Help for a newbee??
-
Hi,
I have just joined the forum and pretty new to Sql 2k. My issue is that I want to copy in some XML into a SQL Table. I keep getting the error in Query Analyser:
Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 18
XML parsing error: XML document must have a top level element.
My code is as follows:
Declare @idoc int
Declare @xmldoc varchar
set @xmldoc = '
<?xml version="1.0" encoding="utf-8"?>
<ArgentinaInputFile">
<Data>
<un_cid>InSTANCEOne</un_cid>
<tnum>12</tnum>
</Data>
<Data>
<un_cid>InstanceTWO</un_cid>
<tnum>12</tnum>
</Data>
</ArgentinaInputFile>
'
EXEC master.dbo.sp_xml_preparedocument @idoc OUTPUT, @xmldoc
SELECT un_cid, tnum FROM OPENXML(@idoc, 'ArgentinaInputFile/Data',2)
WITH ( un_cid varchar(50),
tnum int)
EXEC master.dbo.sp_xml_removedocument @idoc
SELECT * FROM dbo.GarryTest
Can someone please help me as I am stuck. Have no idea what to do?
Thanks in Advance
G MOney
-
Hi G MOney,
Replace the above code with the following:
Declare @idoc int
Declare @xmldoc varchar (2000) --- you haven't specified the size
set @xmldoc = '
<?xml version="1.0" encoding="utf-8"?>
<ArgentinaInputFile> --- you have placed an open double quotation
<Data>
<un_cid>InSTANCEOne</un_cid>
<tnum>12</tnum>
</Data>
<Data>
<un_cid>InstanceTWO</un_cid>
<tnum>12</tnum>
</Data>
</ArgentinaInputFile>
'
EXEC master.dbo.sp_xml_preparedocument @idoc OUTPUT, @xmldoc
SELECT un_cid, tnum FROM OPENXML(@idoc, 'ArgentinaInputFile/Data',2)
WITH (un_cid varchar(50), tnum int)
EXEC master.dbo.sp_xml_removedocument @idoc
SELECT * FROM dbo.GarryTest
Hope this might help you.
Thanks and have a nice day!!!
Lucky -
Yep That works. Thanks Lucky!!!!
-
Hi,
In the example above the I want to change the data type for @xmldoc from varcharr(8000) to text for the reason that me xml file will most likely be up to 5 MB in size.
How can I implment this as sooon as I change the datatype I get the follwoing error msg:
Server: Msg 2739, Level 16, State 1, Line 1
The text, ntext, and image data types are invalid for local variables.
Regards
G Money
-
Ok problem solved an syntax problem.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply