using sp_xml_preparedocument - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

using sp_xml_preparedocument

Author

Topic

jaroot
Starting Member

46 Posts

Posted - 2006-01-13 : 12:20:07

I'm currenlty using this system sp, but the sp either runs in 5ms or 6 minutes passing in the same xml string!

Here's how I'm using it in the sp


EXEC sp_xml_preparedocument @XMLdochandle OUTPUT, @XMLstring
BEGIN TRAN
INSERT INTO(xxx,yyy)
SELECT xxx,yyy
FROM OPENXML (@XMLdochandle,'/xmlRoot/XmlStuff',2)
WITH (xxx int, yyy int)
COMMIT TRAN
EXEC sp_xml_removedocument @XMLdochandle

I'm programming in Coldfusion and it throws back a deadlock error sometimes, other times it just hangs for 6 minutes then processes the request.

Any input on this would be great!
-Jason

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-13 : 12:39:54

I recommend that you
1. Declare a local table to get the OPENXML output.
2. Prepare the XML document.
3. Insert into the declared table from OPENXML.
4. Remove the document.
5. Begin the transaction.
6. Inset into the permanent table from the declared table.
7. Commit the transaction.

This will hold the XML document and memory open for the least amount of time, and will make the run-time of the transaction a lot shorter and reduce the chances for a deadlock.

CODO ERGO SUM

jaroot
Starting Member

46 Posts

Posted - 2006-01-13 : 12:47:57

Good idea.. i'll give that a whirl and let you know how it does

Thanks!

jaroot
Starting Member

46 Posts

Posted - 2006-01-13 : 13:20:58

Ok I tried it.. still hanging up for some reason.

It's really strange because the first time I run the page that does the insert,
the sp runs instantly. THen If I run it again, that's when the hangup happens.

The XML is the same both times.

jaroot
Starting Member

46 Posts

Posted - 2006-01-13 : 13:31:52

I think I may have found out why.. I had the table I was inserting into, open in Enterprise Manager and a had a SELECT * open..

I shut that down and now it doesn't hang up at all any more.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-13 : 14:01:22

It's good that you found the problem.

I would still suggest that you follow that sequence of actions for handling XML documents, expecially if you are doing it a lot. Stored procedure sp_xml_preparedocument uses a lot of memory resources and it is good to minimize the amount of time you are using it. On the transaction side, it is better if you already have the data in table form before you start a transaction to insert it into a table.

CODO ERGO SUM

jaroot
Starting Member

46 Posts

Posted - 2006-01-13 : 16:08:16

Yep I changed it to work like that too. Makes a lot of sense. Thanks for the tips!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources