Viewing information about open XML documents (SQL Server 2005)

Question

Hi,

Could someone please remind me where (which system view?) I can find information about open XML documents in SQL Server 2005?

I'm trying to determine if performance issues we're having with our database could be related to failure to call sp_xml_removedocument. Is it possible, btw, to find out where (e.g. in which stored proc) a document handle was created?

Also, what is the scope or lifetime of an XML document (handle)? E.g. if I have a very short stored proc that calls sp_xml_preparedocument, queries it and then exists, will the XML document get released automatically as soon as the call exits? I understand that it is probably a good (read: best) practise to always call sp_xml_removedocument regardless, but I'd like to know.

Thanks.

---

Professional C# developer

Answer 1

-- Returns information about active handles that have been opened by sp_xml_preparedocument
SELECT *
FROM sys.dm_exec_xml_handles(0)

-- Demo of checking for sp_xml_preparedocument WITHOUT sp_xml_removedocument
SELECT OBJECT_NAME( object_id ) AS missing_sp_xml_removedocument
FROM
	(
	SELECT object_id
	FROM sys.sql_modules
	WHERE definition Like '%sp_xml_preparedocument%'
	EXCEPT
	SELECT object_id
	FROM sys.sql_modules
	WHERE definition Like '%sp_xml_removedocument%'
	) x

The above only works for single occurences of prepare / remove.  You'll have to be a bit more creative if you suspect your procs have mulitple calls!

It's not just good practice to use sp_xml_removedocument; it's compulsory.

"Caution Every document created in memory by sp_xml_preparedocument absolutely has to be removed
from memory by a call to sp_xml_removedocument, with no exceptions. If you fail to remove previously
prepared documents from memory, you can count on memory leaks and regular reboots."

Quote from "Pro SQL Server 2008 XML" by Michael Coles - p57

Also, you should now be moving away from OPENXML and using the XML data-type and methods ( eg value, query, exist and nodes ).

Stop Using OPENXML (Please...)
https://www.sqlservercentral.com/blogs/michael_coles/archive/2008/01/20/stop-using-openxml-please.aspx

Answer 2

Excellent, thanks a lot.

> It's not just good practice to use sp_xml_removedocument; it's compulsory.

Agreed.

> "Caution Every document created in memory by sp_xml_preparedocument absolutely has to be removed
from memory by a call to sp_xml_removedocument, with no exceptions. If you fail to remove previously
prepared documents from memory, you can count on memory leaks and regular reboots."

So are you confirming that XML documents do not get released automatically at the end of a call?

>

Quote from "Pro SQL Server 2008 XML" by Michael Coles - p57

Also, you should now be moving away from OPENXML and using the XML data-type and methods ( eg value, query, exist and nodes ).

Stop Using OPENXML (Please...)
https://www.sqlservercentral.com/blogs/michael_coles/archive/2008/01/20/stop-using-openxml-please.aspx

But does this also apply to SQL Server 2005? The recommendations I've seen (e.g. in this forum) suggest that for SQL Server 2005, at least, OPENXML is still the better alternative with large XML documents.

---

Professional C# developer

Answer 3

No they don't.  You can see this effect by using sp_prepare ... then running the sys.dm_exec_xml_handles query.

You've got a number of options for loading XML in SQL 2005 including SQLXML Bulkload, SSIS, OPENROWSET and of course OPENXML.  I would recommend comparing and contrasting them for performance but the memory "feature" with OPENXML is a big turn-off for me.

Answer 4

Just an addtional information item: The handles live for the duration of your database session. So you should call sp_xml_removedocument as early as possible.

Best regards
Michael

---

-- Michael Rys

Answer 5

Tu say to stop using XML is a recommandation that way applying to SQL2000.  SQL2000 had a very bad implementation for frequent use of  openxml.  This doesn't apply to sql2005 et beyond.