d Gives you information about any sessions that are using xml documents, including stats about the XML document and usage.  You can also get the statements that were used to create the XML handle using sys.dm_exec_sql_text to get the sql.

Type: Function
Parameter: session_id
Data: Snapshot, values based on current reality
Columns:

• session_id – the session_id of the user who is using the sp_xml_preparedocument command to use the XML datatype
• document_id – handle created for the document
• namespace_document_id – you can use sp_xml_preparedocument to declare a document that is  namespace, then use it for subsequent calls. This value will have the value of a document_id, or a NULL if there is not one declared
• sql_handle – relates to sys.dm_exec_sql_text to get the T-SQL of the query
• statement_start_offset – the starting point in the T-SQL query object that is currently executing (note that these values are double the expected values due to unicode values. You will have to divide by 2 when using them in equations. For more information, see examples)
• statement_end_offset – the ending point in the T-SQL query object that is currently executing
• creation_time – the time the handle was created
• original_document_size_bytes – size of the original text of the XML document
• original_namespace_document_size_bytes – size of the original text of the XML namespace document
• num_openxml_calls – Number of times the document has been used in OPENXML calls using this handle
• row_count – Number of rows that have been returned using this handle in OPENXML calls
• dormant_duration_ms – the amount of time (in milliseconds) since the last use of the handle in an OPENXML call

Example:

DECLARE @idoc int
DECLARE @doc varchar(1000)

SET @doc =’
<root>
<person firstName=”barney” lastName=”rubble”/>
<person firstName=”fred” lastName=”flintstone”/>
</root>’

–Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

— Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, ‘/root/person’,1)
                     WITH (firstName varchar(20),
                               lastName varchar(20))

Then you can see the information for this document using:

select *, text, substring(text,statement_start_offset /2,
            1+ statement_end_offset/2 – statement_start_offset/2),
            statement_start_offset,statement_end_offset
from   sys.dm_exec_xml_handles(null)
               cross apply sys.dm_exec_sql_text(sql_handle)

Note:  This is part of an ongoing project to write a book about all of the dynamic management views for Red-Gate. It will be freely distributable once complete as an ebook. Any examples, suggestions, corrections are desired and will certainly be mentioned in the final book.  Also, I have begun maintaining the following web page once the book is closer to completion: http://drsql.org/dmvbook.aspx, including a list of resources I have used to write the book so far.