XML parsing error: Invalid at the top level of the document.

Question

Post reply

XML parsing error: Invalid at the top level of the document.

bmahony993

Right there with Babe

Points: 762
More actions
January 4, 2007 at 3:35 pm

#175440

I am trying to import an XML document into SQL. All the examples I have seen have shown actually typing out the XML data in a stored procedure. What I would like to do is try reference the xml document that is currently on my C drive, and have that document placed into a table. I am trying to pass the path to the xml document in the @strXML variable, but I guess thats not the way to do it! Below is the code for the stored procedure I am using, but I keep getting the same error no matter how I try to amend it. Any help is appreciated.
CREATE PROCEDURE sp_Insert_Projects
    @strXML varchar (8000)
AS
    DECLARE @iDoc int

exec sp_xml_preparedocument @iDoc OUTPUT,@strXML
INSERT INTO allProj (projId, Yr, Title, Bud_Cat, RPM, PADM, ABC, Team, Proj_Grp, Prg_Goal, Class, Notes, Last_Chg, RevCalc, ABC_Office, Calc_True)
      (SELECT * FROM OpenXML(@iDoc, '/AllProj', 2)
                WITH
               (projId VARCHAR(50),
                Yr VARCHAR(4),
                Title VARCHAR(80),
                Bud_Cat VARCHAR(50),
                RPM VARCHAR(6),
                PADM VARCHAR(6),
                ABC VARCHAR(6),
                Team VARCHAR(25),
                Proj_grp VARCHAR(15),
                Prg_Goal VARCHAR(10),
               Class VARCHAR(10),
                Notes VARCHAR(3000),
                Last_Chg SMALLDATETIME,
                RevCalc SMALLINT,
                ABC_Office VARCHAR(50),
                Calc_True BIT)
      )
EXEC sp_xml_removedocument @iDoc
GO

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

the type for @strXML should *not* be varchar(8000) it should be TEXT (you may be truncating the document)

* Noel

bmahony993 Right there with Babe Points: 762 More actions · Answer 2

Ok, I've changed it to text, but that doesn't solve my problem

noeld SSC Guru Points: 96590 More actions · Answer 3

how are you calling the procedure? vb, c# ?

* Noel

noeld SSC Guru Points: 96590 More actions · Answer 4

Oh I see you are trying to use that directly from TSQL. Well the short answer is that you need vb or c# to do that.

* Noel

bmahony993 Right there with Babe Points: 762 More actions · Answer 5

I'm not doing a good job of explaining things!

I am trying to call the stored procedure through VBA (Access) and got these errors, so I tried to to test it in query analyzer to see what happened. As you may have guessed, TSQL is not something I am that familiar with!!

noeld SSC Guru Points: 96590 More actions · Answer 6

This can help you:

http://www.sqlxml.org/faqs.aspx?faq=39

Good Luck,

* Noel

Robert Davis One Orange Chip Points: 28027 More actions · Answer 7

Look up "XML bulk load [SQL Server]" in Books online. Bulk load the XML document into a table and then use sp_XML_preparedocument.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Frans Haesendonckx SSC-Addicted Points: 491 More actions · Answer 8

You should pass the contents of the file, not the path to the file in your procedure. Should the file be larger than 8000 bytes, use TEXTCOPY.exe server-side to import the file into the text column of a temp table and work with that table.

Frans H. ;-))

Robert Davis One Orange Chip Points: 28027 More actions · Answer 9

This is SQL 2005. Don't use Text data type/columns. Use XML or varchar(max) or nvarchar(max) data type/columns. And use the SQL Bulk Import I mentioned.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

bmahony993 Right there with Babe Points: 762 More actions · Answer 10

Working in VBA and using the SQL Bulk Import as follows :

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")

objBL.ConnectionString = "provider=SQLOLEDB.1;data source=myDB;database=Test;uid=test_Admin;pwd=nelson"

objBL.Execute "C:\schema.xml", "C:\Project.xml"

I keep getting the same error which is:

Schema: relationship expected on "Project"

and I am not sure how to fix that, if possible. Any suggestions?

Thanks in advance!

jmclain Valued Member Points: 74 More actions · Answer 11

Did you ever come to a (simple) resolution to your problem, like removing the top line in your xml document or something like that? I'm having the same problem, but calling the procedure directly from SQL Server Management Studio...

Signed,

also a grasshopper : )

bmahony993 Right there with Babe Points: 762 More actions · Answer 12

Actually, I never did find a way around that using bulkload. Instead, I used the sp_xml_preparedocument and that worked really well.