April 16, 2003 at 3:25 pm
Here's my problem. I want to create a generic stored procedure that will take XML documents, determine what type of document they are from within the XML and then dynamically create an insert statement to take the XML and put it in a specific table. The SP basically is:
create proc BatchXML
@ntext text
as
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @ntext
SELECT @doctype = localname
FROM OPENXML (@idoc, '',2)
where id = 0
exec GetInsertString @doctype, @idoc, @InsertString output
exec (@InsertString)
The SP GetInsertString creates the insert statement in the format
Insert table (fields)
select fields
from openxml(@idoc, xml_path,3)
with (field1 type1,
field2, type2,
etc..)
the problem is that when I call the exec it can't find the xml document referenced with @idoc. I'm thinking that the exec call is in a different memory space than the calling SP. However I can't create a SUPER dynamic SP because @ntext is a text field and you can't concatenate them nor define them within an SP.
Any ideas?
April 21, 2003 at 12:00 pm
This was removed by the editor as SPAM
May 2, 2003 at 12:41 am
I'm currently trying to do some dynamic XML uploading to the SQL too (and struggling a little)
What about determining the type of XML document in the first proc, then passing that plus the original XML string through to the second, and then doing the insert.
Depends where you're calling the stored procs from, but you could also seperate the calls from within VB or ActiveX script.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply