May 18, 2004 at 9:35 am
The company i work for uses cold fusion server for web applications. Previously, we sent data from our desktop application to our sql database via a web page. the reason for this is the desktop app is widely distributed in schools and other places with firewalls that are pretty restrictive on outgoing data. Port 80 (web pages) are generally open, so we send the packet via an html form post to our web page.
Our developers had used cold fusion to parse the xml and write it to the db, but it was incredibly slow. I was tasked with redoing the template, but doing all the xml parsing and processing on the sql server.
What i have done is, collect the xml and parse it in a t-sql script. Ok, finally the question: I can't seem to pass a huge xml packet to a stored procedure. It fails, though i can easily parse the xml data if i just include the contents of my would be stored procedure right inside my cfquery tags.
Does anyone know of a way to pass a very large xml packet to a stored procedure?
May 18, 2004 at 2:06 pm
How big is "a very large xml packet" in your world? Is it larger than the parameter supports? For example, are you trying to pass more than 8000 characters using a varchar(8000)?
A workaround I have used is for the upper stack to simply insert the xml packet into a text field, and then work with the text field inside the stored proc. This way the upper stack is very fast as all it has to do is a simple insert, then pass a row identifier to the stored proc. The stored proc is also somewhat more efficient because it does not have to accept a large block of character data.
It works for me... Your mileage may vary.
Cheers
Wayne
May 18, 2004 at 3:10 pm
Yes, much bigger than varchar(8000). I can work with the xml packet with no issues. the abbreviated look at how i do this is as follows:
EXEC sp_xml_preparedocument @sqdoc OUTPUT, '<cfoutput>#xmlQuestions2#</cfoutput>'
The cfoutputs just spit out the huge xml packet and this works fine with no data loss.
what i can't do is pass this to a stored procedure, such as
exec mystoredproc @xmlpacket='<cfoutput>#xmlQuestions2#</cfoutput>'
So, to clarify, the issue isnt doing what i need to, its just that i want to reuse that code by making it a stored procedure and just pass the packets back to the server, rather than run the t-sql via my web page.
thanks for the help though
May 19, 2004 at 6:45 am
Not familiar with ColdFusion, but if you were using ADO, use the command object. Something like:
With cmd
.CommandText = "sp_xml_preparedocument"
.Parameters("@xmlpacket") = "<cfoutput>#xmlQuestions2#</cfoutput>"
.Execute
End With
should work. I assume there is an equivalent in CF?
As a side note, I'd be interested to know how you're parsing XML in a TEXT field within a stored procedure.
May 19, 2004 at 8:27 am
What you are saying to do in asp is what i am trying to do the equivelant of in cold fusion. Our desktop application creates an html form and submits it to our webpage. the xml packets are very large, over the 8000 char limit. In cold fusion, i receive variables, which look like #xmlquestions# or whatever. the variable it self is a standard formatted xml packet, and very large.
What i am doing now is, i open a connection to the database, which is what i would do if i was going to call the stored procedure. The syntax is like this
<cfquery name="myquery" datasource="myodbcdatasource">
contents of my query. currently, i can parse teh xmldoc right here by doing this, (#xmlquestions is the form submitted textarea from my app)
EXEC sp_xml_preparedocument @sqdoc OUTPUT, '<cfoutput>#xmlQuestions2#</cfoutput>'
then following i do a ton of processing, including a couple of while loops. the database script is 600 lines long because of the complexity of the data.
</cfquery>
If I could instead store that 600 lines in a stored procedure, and just pass my xml packet over like this
<cfquery name="myquery" datasource="mydatasource">
exec mystoreproc @xmlpacket='#xmlQuestions2#'
</cfquery>
then i could obviously push some of the transaction to the backend and still reuse that code more easily.
thanks,
May 26, 2004 at 2:50 pm
Here is a sample, using the input as a text object.
--first , create the stored procedure.
if exists (select * from sysobjects
where id = object_id('dbo.usp_insert_pubs_authors') and sysstat & 0xf = 4)
drop procedure dbo.usp_insert_pubs_authors
GO
CREATE PROCEDURE usp_insert_pubs_authors (
@xml_doc TEXT )
AS
SET NOCOUNT ON
DECLARE @hdoc INT -- handle to XML doc
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @XML_Doc
-- build a table (variable table) to store the xml-based result set
DECLARE @storesinsert TABLE (
stor_id varchar(16) ,
stor_name varchar(16) ,
stor_address varchar(16) ,
city varchar(16) ,
state varchar(16) ,
zip varchar(16)
)
INSERT @storesinsert
SELECT
stor_id ,
stor_name ,
stor_address ,
city ,
state ,
zip
FROM
-- use the correct XPath .. the second arg ("2" here) distinquishes
-- between textnode or an attribute, most times with
--.NET typed datasets, its a "2"
OPENXML (@hdoc, '/StoresDS/store', 2) WITH (
stor_id varchar(16) ,
stor_name varchar(16) ,
stor_address varchar(16) ,
city varchar(16) ,
state varchar(16) ,
zip varchar(16)
)
-- temp select just to show all the data in the @variabletable
select * from @storesinsert
BEGIN TRANSACTION
--insert into the stores table ( a "real" table) .. (from) the @variabletable (which now contains all the dataset data)
Insert into stores
(
stor_id ,
stor_name ,
stor_address ,
city ,
state ,
zip
)
SELECT
stor_id ,
stor_name ,
stor_address ,
city ,
state ,
zip
FROM @storesinsert
IF @@ERROR <> 0
BEGIN
RAISERROR('error updating database', 16, 1)
ROLLBACK Transaction
END
COMMIT TRAN
GO
--
--Now run the code using an example
--
--Here is the call. The input for the t-sql proc is
--text, but tsql doesn't allow localized text parameters
--so varchar is used here (for @xml_doc_temp).
--The xml string (below) follows what would be a typed dataset xml
--While here, it is manually created, the procedure would
--probably be called using the myTypeDataset.getXML() as the input parameter
--Naturally,the xml can contain 0, 1 or N number of "stores" for
--insertion (2 stores are used here)
declare @xml_doc_temp varchar(8000)
select @xml_doc_temp =
'
<StoresDS>
<store>
<stor_id>' + LEFT(NEWID(), 4) + '</stor_id>
<stor_name>' + LEFT(NEWID(), 16) + '</stor_name>
<stor_address>456 Hickory</stor_address>
<city>Charlotte</city>
<state>NC</state>
<zip>44444</zip>
</store>
<store>
<stor_id>' + LEFT(NEWID(), 4) + '</stor_id>
<stor_name>' + LEFT(NEWID(), 16) + '</stor_name>
<stor_address>456 Main</stor_address>
<city>Charlotte</city>
<state>NC</state>
<zip>33333</zip>
</store>
</StoresDS>
'
print @xml_doc_temp
EXEC usp_insert_pubs_authors @xml_doc_temp
GO
select * from stores order by stor_name
Maybe that will get you started. I've not had trouble passing in alot of data. Of course, I'm not using CF, I'm using .NET, so ... I don't know.
..
May 26, 2004 at 2:57 pm
Thanks for your input, but this wont work for me. you are sending a varchar xml packet to your stored procedure, of no more than 8000 chars. My xml packets are substantially larger than that, and therefore must be of type text, hence my original question.
thanks anyhow
May 26, 2004 at 2:58 pm
p.s. this is is not a limitation of Cold Fusion. SQL doesn't accept a text field as large as my xml packets in a stored procedure call. I really don't think it would matter how I instantiate the database call to execute the procedure. (asp, .net, cold fusion, php, vbscript, etc, etc, etc.)
thanks,
May 27, 2004 at 6:38 am
No.... the procedure itself takes a "text" parameter.
CREATE PROCEDURE usp_insert_pubs_authors (
@xml_doc TEXT )
My ~~example~~ (to call the procedure in t-sql) uses a varchar, and only because you cannot declare a local variable of type 'text'. And a varchar will translate "up" to a text, not vice versa.
The defintion of a text datatype is:
text: Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters.
So there's the limitation of my procedure above.
May 27, 2004 at 7:48 am
I got you, i see that now. This brings back my original point, as this is exactly how i created the procedure. I know you cant use text variables as a local variable, but that you can create the procedure to receive a text variable. I did this, but when i call the procedure with the text packet (for the variable) it doesnt throw an error, but it fails because the incoming packet is truncated ( i think by sql).
I guess my question is why stored procedures accepting text variables would truncate the variable?
June 29, 2004 at 1:51 pm
That I dont' know.
Maybe you can write a VBS script to try it, and see if its a CF issue or not.
I've written one CF page in my life, to call a VB COM object.
THe good news is that while I haven't worked at that company for over 2 years, they're still using my one CF page I wrote.
..
You could also create a ~real table (but temporary in the sense you will get rid of it when you get this resolved)
And right at the beginning of the procedure, take the @xml_doc text variable and insert into the debug table you created, just to see if its at the beginning its getting truncated.
CREATE TABLE tblDebug AS
( uid int IDENTITY (-32000, 1) ,
mytext text )
..then in your procedure do a:
INSERT into tblDEBUG values (@xml_doc )
..
then check it in the table.
I don't know?? Just trying to offer something.
.
April 22, 2005 at 9:03 am
I too am using the same thing. We receive dumps of data in the xml format. I have created a stored procedure which accepts text as the input parameter. I insert appropriate data into appropriate tables using OPENXML. Whole thing worked fine. However I cannot execute this stored procedure (sp_load '@the whole file copied..not the path edits in textpad' ) in the sql query window (since the window limit is 64kb) I had to use isql command line prompt.
This worked fine until one of the tables had more than 1000 rows getting inserted at a time. I started having "insufficient memory" error. If this helps you, I can give you more details and you can troubleshoot beyond that time.
I am just going to use Datadirect xml driver and schedule a DTS package works g8.
Thanks
April 22, 2005 at 9:08 am
I too am using the same thing. We receive dumps of data in the xml format. I have created a stored procedure which accepts text as an input parameter. I insert appropriate data into appropriate tables using OPENXML. Whole thing worked fine. very fast too. However I cannot execute this stored procedure (sp_load 'xmlfile') in the sql query window (since the window limit is 64kb) I had to use isql command line prompt.
This worked fine until one of the tables had more than 1000 rows getting inserted at a time. I started having "insufficient memory" error. If this helps you, I can give you more details and you can troubleshoot beyond this point.
I am just going to use Datadirect xml driver and schedule a DTS package works g8. Fast and perfect! You can download a trial version for 15 days.
1000 rows problem could be SQL server / database issue or isql issue. May not happen with you.
Thanks
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply