{"id":928,"date":"2014-11-18T17:36:10","date_gmt":"2014-11-18T17:36:10","guid":{"rendered":"http:\/\/elbsolutions.com\/projects\/?p=928"},"modified":"2022-02-03T11:25:00","modified_gmt":"2022-02-03T17:25:00","slug":"excel-xml-avoid-sql-data-connection","status":"publish","type":"post","link":"https:\/\/elbsolutions.com\/projects\/excel-xml-avoid-sql-data-connection\/","title":{"rendered":"Excel to XML to avoid direct OPENROWSOURCE SQL data connection to Excel"},"content":{"rendered":"<p>The company I am currently solving issues for will not accept Excel as a table data source. This I know, so therefore a really really great report cannot go on the live system. So I have been beating my head on how to get around the politics &#8211; good politics because Excel is a breakable table (which is why they won&#8217;t\/don&#8217;t like it). But &#8230; how do we move forward AND satisfy IT? Well, an interim solution would be to use XML as the SQL Server data source and then build a url driven Service to extract data from Excel into this XML data source. So &#8230; links to get started you ask?<\/p>\n<ul>\n<li><a href=\"http:\/\/msdn.microsoft.com\/en-ca\/library\/dd220468.aspx\" target=\"_blank\" rel=\"noopener noreferrer\">http:\/\/msdn.microsoft.com\/en-ca\/library\/dd220468.aspx<\/a><\/li>\n<li><a href=\"https:\/\/code.msdn.microsoft.com\/office\/How-to-convert-excel-file-7a9bb404\" target=\"_blank\" rel=\"noopener noreferrer\">https:\/\/code.msdn.microsoft.com\/office\/How-to-convert-excel-file-7a9bb404<\/a><\/li>\n<\/ul>\n<p>Can life get easier? What will the world be like in 10 years. This took me 5 minutes to see if we could do it. Now it is an interim solution because Excel is still the data source, but all we have to do now is replace Excel with an official app on the web in the near future &#8211; but to do this properly requires many many weeks of development, whereas a service &#8211; either works or doesn&#8217;t and only requires unit tests, not people to test is my simplistic view on this.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The company I am currently solving issues for will not accept Excel as a table data source. This I know, so therefore a really really great report cannot go on the live system. So I have been beating my head on how to get around the politics &#8211; good politics because Excel is a breakable [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-928","post","type-post","status-publish","format-standard","hentry","category-general"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.7 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Excel to XML to avoid direct OPENROWSOURCE SQL data connection to Excel - ELB Solutions.com Inc.<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/elbsolutions.com\/projects\/excel-xml-avoid-sql-data-connection\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excel to XML to avoid direct OPENROWSOURCE SQL data connection to Excel - ELB Solutions.com Inc.\" \/>\n<meta property=\"og:description\" content=\"The company I am currently solving issues for will not accept Excel as a table data source. This I know, so therefore a really really great report cannot go on the live system. So I have been beating my head on how to get around the politics &#8211; good politics because Excel is a breakable [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/elbsolutions.com\/projects\/excel-xml-avoid-sql-data-connection\/\" \/>\n<meta property=\"og:site_name\" content=\"ELB Solutions.com Inc.\" \/>\n<meta property=\"article:published_time\" content=\"2014-11-18T17:36:10+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-02-03T17:25:00+00:00\" \/>\n<meta name=\"author\" content=\"Etienne Bley\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Etienne Bley\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/excel-xml-avoid-sql-data-connection\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/excel-xml-avoid-sql-data-connection\\\/\"},\"author\":{\"name\":\"Etienne Bley\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#\\\/schema\\\/person\\\/51e717c68f4f5917c63baf88f0896c39\"},\"headline\":\"Excel to XML to avoid direct OPENROWSOURCE SQL data connection to Excel\",\"datePublished\":\"2014-11-18T17:36:10+00:00\",\"dateModified\":\"2022-02-03T17:25:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/excel-xml-avoid-sql-data-connection\\\/\"},\"wordCount\":236,\"articleSection\":[\"General\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/excel-xml-avoid-sql-data-connection\\\/\",\"url\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/excel-xml-avoid-sql-data-connection\\\/\",\"name\":\"Excel to XML to avoid direct OPENROWSOURCE SQL data connection to Excel - ELB Solutions.com Inc.\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#website\"},\"datePublished\":\"2014-11-18T17:36:10+00:00\",\"dateModified\":\"2022-02-03T17:25:00+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#\\\/schema\\\/person\\\/51e717c68f4f5917c63baf88f0896c39\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/excel-xml-avoid-sql-data-connection\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/excel-xml-avoid-sql-data-connection\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/excel-xml-avoid-sql-data-connection\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel to XML to avoid direct OPENROWSOURCE SQL data connection to Excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#website\",\"url\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/\",\"name\":\"ELB Solutions.com Inc.\",\"description\":\"Bringing all your IT Pieces together\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#\\\/schema\\\/person\\\/51e717c68f4f5917c63baf88f0896c39\",\"name\":\"Etienne Bley\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g\",\"caption\":\"Etienne Bley\"},\"url\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/author\\\/etienne-bley\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Excel to XML to avoid direct OPENROWSOURCE SQL data connection to Excel - ELB Solutions.com Inc.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/elbsolutions.com\/projects\/excel-xml-avoid-sql-data-connection\/","og_locale":"en_US","og_type":"article","og_title":"Excel to XML to avoid direct OPENROWSOURCE SQL data connection to Excel - ELB Solutions.com Inc.","og_description":"The company I am currently solving issues for will not accept Excel as a table data source. This I know, so therefore a really really great report cannot go on the live system. So I have been beating my head on how to get around the politics &#8211; good politics because Excel is a breakable [&hellip;]","og_url":"https:\/\/elbsolutions.com\/projects\/excel-xml-avoid-sql-data-connection\/","og_site_name":"ELB Solutions.com Inc.","article_published_time":"2014-11-18T17:36:10+00:00","article_modified_time":"2022-02-03T17:25:00+00:00","author":"Etienne Bley","twitter_misc":{"Written by":"Etienne Bley","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/elbsolutions.com\/projects\/excel-xml-avoid-sql-data-connection\/#article","isPartOf":{"@id":"https:\/\/elbsolutions.com\/projects\/excel-xml-avoid-sql-data-connection\/"},"author":{"name":"Etienne Bley","@id":"https:\/\/elbsolutions.com\/projects\/#\/schema\/person\/51e717c68f4f5917c63baf88f0896c39"},"headline":"Excel to XML to avoid direct OPENROWSOURCE SQL data connection to Excel","datePublished":"2014-11-18T17:36:10+00:00","dateModified":"2022-02-03T17:25:00+00:00","mainEntityOfPage":{"@id":"https:\/\/elbsolutions.com\/projects\/excel-xml-avoid-sql-data-connection\/"},"wordCount":236,"articleSection":["General"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/elbsolutions.com\/projects\/excel-xml-avoid-sql-data-connection\/","url":"https:\/\/elbsolutions.com\/projects\/excel-xml-avoid-sql-data-connection\/","name":"Excel to XML to avoid direct OPENROWSOURCE SQL data connection to Excel - ELB Solutions.com Inc.","isPartOf":{"@id":"https:\/\/elbsolutions.com\/projects\/#website"},"datePublished":"2014-11-18T17:36:10+00:00","dateModified":"2022-02-03T17:25:00+00:00","author":{"@id":"https:\/\/elbsolutions.com\/projects\/#\/schema\/person\/51e717c68f4f5917c63baf88f0896c39"},"breadcrumb":{"@id":"https:\/\/elbsolutions.com\/projects\/excel-xml-avoid-sql-data-connection\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/elbsolutions.com\/projects\/excel-xml-avoid-sql-data-connection\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/elbsolutions.com\/projects\/excel-xml-avoid-sql-data-connection\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/elbsolutions.com\/projects\/"},{"@type":"ListItem","position":2,"name":"Excel to XML to avoid direct OPENROWSOURCE SQL data connection to Excel"}]},{"@type":"WebSite","@id":"https:\/\/elbsolutions.com\/projects\/#website","url":"https:\/\/elbsolutions.com\/projects\/","name":"ELB Solutions.com Inc.","description":"Bringing all your IT Pieces together","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/elbsolutions.com\/projects\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/elbsolutions.com\/projects\/#\/schema\/person\/51e717c68f4f5917c63baf88f0896c39","name":"Etienne Bley","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g","caption":"Etienne Bley"},"url":"https:\/\/elbsolutions.com\/projects\/author\/etienne-bley\/"}]}},"_links":{"self":[{"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/posts\/928","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/comments?post=928"}],"version-history":[{"count":3,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/posts\/928\/revisions"}],"predecessor-version":[{"id":2777,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/posts\/928\/revisions\/2777"}],"wp:attachment":[{"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/media?parent=928"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/categories?post=928"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/tags?post=928"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}