the importance of sp_xml_removedocument
When I deal with multiple inserts/updates/deletes in SQL server and in stored procedures, I use XML to do this. This is a practice I learned from my friend, Bear, who happens to be an amazing Oracle DBA. For example, say I want to assign a group or many groups to a user, in my DAO, I would create an XML document like this:
<xml_rec>
<user_id>10001</user_id>
<group_id>234</group_id>
</xml_rec>
<xml_rec>
<user_id>20104</user_id>
<group_id>556</group_id>
</xml_rec>
</xml_tran>
Then I would pass that XML to a stored procedure (in my DAO) and have that procedure rip through the XML and do multiple inserts in an implicit transaction. So basically, if it fails it will roll back all of the inserts. Here is what a stored procedure (in SQL Server) would look like to do multiple inserts via XML.
@UserGroupXML TEXT
AS
BEGIN
DECLARE @nDoc INT
EXEC sp_xml_preparedocument @nDoc OUTPUT, @UserGroupXML
INSERT INTO dbo.UserGroup
(User_ID,Group_ID)
SELECT user_id, group_id
FROM OPENXML (@nDoc, '/xml_tran/xml_rec',2)
WITH (user_id,group_id)
EXEC sp_xml_removedocument @nDoc
END
Now, there are a few things to mention in this stored procedure. To use XML with SQL Server, you have to create a temporary variable (XML document) in memory so SQL can rip through it and do the operation it is told to do on the data. The biggest thing to remember is when you create an XML doc, you need to destroy it. Online docs states this, and I quote, "A parsed document is stored in the internal cache of Microsoft SQL Server 2000. The MSXML parser uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory."
So the other day, I was doing this same thing in a VERY high transaction per minute environment, and I forgot to use the sp_xml_removedocument. So, users were getting an error saying "Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]XML document could not be created because server memory is low. Use sp_xml_removedocument to release XML documents." Basically what was happening was I just filled up the cache (1/8th of 8 GB of server memory) and SQL Server could not do this transaction any more until the cache was cleared.
The good thing is, we reran the stored procedure and it cleared itself up. It does say in the SQL Server online docs that this cache is cleared when a connection is lost or reset. So, in our ColdFusion world, this is done by either restarting ColdFusion (or an instance of ColdFusion) that connects to the SQL Server, or by going into the CF Admin, clicking Datasources, then the datasource, then Show Advanced Settings, and then the Disable Connections. Submit those changes and then go back and enable the connection again.
We use this extensively with our Jobs2Web product where we're taking in large xml job feeds. Using this technique it extremely powerful and MUCH quicker than having cfusion do this type of processing - plus the data is all together and available all at the same time.
example: After you have created your temp memory table with Declare @tmpTbl TABLE etc.
<div class="code">
INSERT INTO @tmpTbl
SELECT
title, description, enddate, location, internalid, applyurl, department
FROM
OPENXML(@hDoc, N'/jobs/job', 2 )
</div>
Another thing worth noting is that when testing this type of procedure in MS SQL management studio - you'll likely need to change the datatype from Text to varchar(8000) in order to get it to run properly to test it (at least that's how it was in 2k).