Returns information about active handles that have been opened by sp_xml_preparedocument.
session_id | 0,
ID of the session. If session_id is specified, this function returns information about XML handles in the specified session. If 0 is specified, the function returns information about all XML handles for all sessions
The lifetime of sql_handles used to retrieve the SQL text that executed a call to sp_xml_preparedocument outlives the cached plan used to execute the query. If the query text is not available in the cache, the data cannot be retrieved using the information provided in the function result. This can occur if you are running many large batches.
Requires VIEW SERVER STATE permission on the server to see all sessions or session IDs that are not owned by the caller. A caller can always see the data for his or her own current session ID.
The following example selects all active handles.
select * from sys.dm_exec_xml_handles(0)