January 4, 2007 at 3:35 pm
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
January 4, 2007 at 3:49 pm
the type for @strXML should *not* be varchar(8000) it should be TEXT (you may be truncating the document)
* Noel
January 4, 2007 at 3:51 pm
Ok, I've changed it to text, but that doesn't solve my problem
January 4, 2007 at 3:53 pm
how are you calling the procedure? vb, c# ?
* Noel
January 4, 2007 at 3:55 pm
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
January 4, 2007 at 4:01 pm
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!!
January 4, 2007 at 4:10 pm
January 4, 2007 at 11:12 pm
Look up "XML bulk load [SQL Server]" in Books online. Bulk load the XML document into a table and then use sp_XML_preparedocument.
January 5, 2007 at 3:05 am
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. ;-))
January 5, 2007 at 12:13 pm
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.
January 8, 2007 at 4:20 pm
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!
July 30, 2008 at 7:03 am
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 : )
July 30, 2008 at 9:05 am
Actually, I never did find a way around that using bulkload. Instead, I used the sp_xml_preparedocument and that worked really well.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply