{"id":79,"date":"2017-05-30T15:20:35","date_gmt":"2017-05-30T15:20:35","guid":{"rendered":"http:\/\/www.polysyncronism.com\/wordpress\/?p=79"},"modified":"2017-05-30T17:48:45","modified_gmt":"2017-05-30T17:48:45","slug":"visualize-sql-xml-execution-plan-using-html","status":"publish","type":"post","link":"http:\/\/www.polysyncronism.com\/wordpress\/2017\/05\/30\/visualize-sql-xml-execution-plan-using-html\/","title":{"rendered":"Visualize SQL XML Execution Plan using HTML"},"content":{"rendered":"<p>I&#8217;ve always used the Microsoft SQL Management Studio Execution plan feature to visualize the query plan.  However today I was experimenting with the SQL XML showplan statement to see if I could learn anything more about execution plans by looking more closely at the code.  As soon as I saw the XML it occurred to me that I could use XSLT to convert the output to HTML similar to the way that the SQL Management studio did it.  I was curious to try, but before I writing my own, I checked to see if anyone else did it already and found this on github:<\/p>\n<ul>\n<li><a href=\"http:\/\/www.polysyncronism.com\/wordpress\/2017\/05\/25\/sql-convert-time-to-decimal\/\">JustinPealing\/html-query-plan<\/a><\/li>\n<\/ul>\n<p>So, to demonstrate I created a simple SQL using temp tables so that I could perform some very basic tests. <\/p>\n<pre class=\"lang:tsql decode:true \" title=\"Create Temp Tables\" >IF OBJECT_ID('tempdb..#header') IS NOT NULL DROP TABLE #header\r\nIF OBJECT_ID('tempdb..#detail') IS NOT NULL DROP TABLE #detail\r\n\r\nset nocount on\r\n\r\ncreate table #header\r\n(\r\n order_no int,\r\n name  varchar(30),\r\n email varchar(255),\r\n)\r\n\r\n create table #detail\r\n(\r\n order_no int,\r\n item  varchar(30),\r\n item_code  varchar(30)\r\n )\r\n\r\n<\/pre>\n<p>Insert some sample data <\/p>\n<pre class=\"lang:tsql decode:true \" title=\"Inserts\" > insert into #header (order_no,name,email) values (121,'Joe Blogs','j.bloggs@bar.com')\r\n insert into #header (order_no,name,email) values (122,'Bill Smith','B.Smith@foo.com')\r\n\r\n insert into #detail (order_no,item,item_code) values (121,'Item 1','IT-001')\r\n insert into #detail (order_no,item,item_code) values (121,'Item 1','IT-002')\r\n insert into #detail (order_no,item,item_code) values (121,'Item 1','EV-003')\r\n \r\n insert into #detail (order_no,item,item_code) values (122,'Item 1','IT-001')\r\n insert into #detail (order_no,item,item_code) values (122,'Item 2','IT-002')\r\n insert into #detail (order_no,item,item_code) values (122,'Item 3','IT-003')<\/pre>\n<p>Turn on the SHOWPLAN_XML in SQL and run query to produce the XML.<\/p>\n<pre class=\"lang:tsql decode:true \" title=\"Create Sample Query and XML with plan\" >\r\nSET SHOWPLAN_XML ON;\r\ngo\r\n SELECT\r\n      h.order_no\r\n    , h.name\r\n    , h.email\r\n    , d.item\r\n    , d.item_code\r\nFROM #header h\r\nJOIN #detail AS d ON h.order_no = d.order_no\r\nWHERE NOT EXISTS (\r\n      SELECT NULL\r\n      FROM #detail od\r\n      WHERE od.item_code = 'EV-003'\r\n      AND h.order_no = od.order_no\r\n      )\r\nGO\r\nSET SHOWPLAN_XML Off;\r\nGO<\/pre>\n<p>Then copy and paste the output XML data into a file and upload to the demo page that uses Justins HTML query plan visualizer library.<\/p>\n<p><a href=\"http:\/\/www.polysyncronism.com\/demo\/2017053001\/test01.html\" target=\"_blank\">HTML XML Upload demo<\/a><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/www.polysyncronism.com\/wordpress\/wp-content\/uploads\/2017\/05\/test1-300x194.jpg\" alt=\"\" width=\"300\" height=\"194\" class=\"alignnone size-medium wp-image-85\" srcset=\"http:\/\/www.polysyncronism.com\/wordpress\/wp-content\/uploads\/2017\/05\/test1-300x194.jpg 300w, http:\/\/www.polysyncronism.com\/wordpress\/wp-content\/uploads\/2017\/05\/test1.jpg 591w\" sizes=\"auto, (max-width: 300px) 85vw, 300px\" \/><\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;ve always used the Microsoft SQL Management Studio Execution plan feature to visualize the query plan. However today I was experimenting with the SQL XML showplan statement to see if I could learn anything more about execution plans by looking more closely at the code. As soon as I saw the XML it occurred to &hellip; <a href=\"http:\/\/www.polysyncronism.com\/wordpress\/2017\/05\/30\/visualize-sql-xml-execution-plan-using-html\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;Visualize SQL XML Execution Plan using HTML&#8221;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,7],"tags":[],"class_list":["post-79","post","type-post","status-publish","format-standard","hentry","category-code","category-t-sql"],"_links":{"self":[{"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/posts\/79","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/comments?post=79"}],"version-history":[{"count":6,"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/posts\/79\/revisions"}],"predecessor-version":[{"id":88,"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/posts\/79\/revisions\/88"}],"wp:attachment":[{"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/media?parent=79"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/categories?post=79"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/tags?post=79"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}