When we will open the XML doc using stored procedure sp_xml_preparedocument in sql server while we are not removing it by executing the procedure:
EXECUTE SP_XML_REMOVEDOCUMENT @hdoc
We may get error message:
XML document could not be created because server memory is low. Use sp_xml_removedocument to release XML documents.
Get the session_Id by executing the following sql query which is holding the active handler opened by sp_xml_preparedocument
SELECT * FROM SYS.DM_EXEC_XML_HANDLES(0)
Step 2: Now kill the all the session_Id one by one which has returned by above query:
It will solve this problem!!