Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument - Urgent Help for a newbee??

• G Money-291556

  SSChasing Mays

  Points: 633

  More actions

  January 6, 2006 at 9:15 am

  #152690

  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

• lucky-80472

  SSCertifiable

  Points: 7793

  More actions

  January 6, 2006 at 11:33 am

  #613353

  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

• G Money-291556

  SSChasing Mays

  Points: 633

  More actions

  January 6, 2006 at 1:55 pm

  #613389

  Yep That works. Thanks Lucky!!!!

• G Money-291556

  SSChasing Mays

  Points: 633

  More actions

  January 9, 2006 at 2:00 am

  #613470

  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

• G Money-291556

  SSChasing Mays

  Points: 633

  More actions

  January 9, 2006 at 6:43 am

  #613514

  Ok problem solved an syntax problem.