This is from the SQL Server 2000 BOL:
"Note A parsed document is stored in the internal cache of 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 it seems to me that if you're running out of memory, you'll have to up the memory allocated to SQL Server.
-Mike