d Gives you information about any sessions that are using xml documents, including stats about the XML document and usage.\u00a0 You can also get the statements that were used to create the XML handle using sys.dm_exec_sql_text to get the sql.<\/p>\n
Type:<\/b> Function Example:<\/b><\/p>\n DECLARE @idoc int SET @doc =’ –Create an internal representation of the XML document. — Execute a SELECT statement that uses the OPENXML rowset provider. Then you can see the information for this document using:<\/p>\n select *, text, substring(text,statement_start_offset \/2, Note:\u00a0 This is part of an ongoing project to write a book about all of the dynamic management views for <\/em>Red-Gate<\/em><\/a>.\u00a0It will be freely distributable once complete as an ebook.\u00a0Any examples, suggestions, corrections are desired and will certainly be mentioned in the final book.\u00a0 Also, I have begun maintaining the following web page once the book is closer to completion: <\/em>http:\/\/drsql.org\/dmvbook.aspx<\/em><\/a>, including a list of resources I have used to write the book so far.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":" d Gives you information about any sessions that are using xml documents, including stats about the XML document and usage.\u00a0 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……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-82024","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82024","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=82024"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82024\/revisions"}],"predecessor-version":[{"id":84343,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82024\/revisions\/84343"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82024"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82024"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82024"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82024"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nParameter: <\/b>session_id
\nData:<\/b> Snapshot, values based on current reality
\nColumns:<\/b><\/p>\n\n
\nDECLARE @doc varchar(1000)<\/p>\n
\n<root>
\n<person firstName=”barney” lastName=”rubble”\/>
\n<person firstName=”fred” lastName=”flintstone”\/>
\n<\/root>’<\/p>\n
\nEXEC sp_xml_preparedocument @idoc OUTPUT, @doc<\/p>\n
\nSELECT\u00a0\u00a0\u00a0 *
\nFROM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OPENXML (@idoc, ‘\/root\/person’,1)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WITH (firstName varchar(20),
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 lastName varchar(20))<\/p>\n
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1+ statement_end_offset\/2 – statement_start_offset\/2),
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 statement_start_offset,statement_end_offset
\nfrom\u00a0\u00a0 sys.dm_exec_xml_handles(null)<\/strong>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cross apply sys.dm_exec_sql_text(sql_handle)<\/p>\n