title: Loading (large) XML data into Azure SQL database table
body: <p>I have been working on a solution for transferring a large XML file into Azure SQL database, thus far unsuccessfully. The XML file is accessed via a fixed URL. I have no previous experience using SQL or Azure before this project, so I apologise if I've missed something obvious.</p>
  
  <p>I setup the database with appropriate tables, I just need to populate the tables with my data.</p>
  
  <p>The XML file comes in the following format, however there are several thousand jobs. The file, when downloaded, is approximately 1.2Mb however I want to access it from the URL not local computer.</p>
  
  <pre><code>&lt;Response api-method="Current"&gt;
    &lt;Status&gt;OK&lt;/Status&gt;
       &lt;Jobs&gt;
          &lt;Job&gt;
             &lt;ID&gt;N1234&lt;/ID&gt;
             &lt;Name&gt;Job name here&lt;/Name&gt;
             &lt;Description/&gt;Job description here&lt;/Description
             &lt;StartDate&gt;2018-10-08T00:00:00&lt;/StartDate&gt;
             &lt;DueDate&gt;2018-10-21T00:00:00&lt;/DueDate&gt;
             &lt;Manager&gt;
                &lt;ID&gt;12345&lt;/ID&gt;
                &lt;Name&gt;John Smith&lt;/Name&gt;
             &lt;/Manager&gt;
          &lt;/Job&gt;
          &lt;Job&gt;
              Repeat above several thousand times
          &lt;/Job&gt;
       &lt;/Jobs&gt;
  &lt;/Response&gt;
  </code></pre>
  
  <p>So far my most successful method works using some built-in Azure Logic Apps. I am using HTML GET to load the XML data, then passing it into the Liquid app which converts XML into JSON. I then expect JSON format can be easily loaded into a database table. This Logic App successfully converts the XML to JSON, but only when I strip the file down to ~80 jobs. Hence I know that my logic app works and that the file size is my issue.</p>
  
  <p><a href="https://i.stack.imgur.com/Jq6ic.png" rel="nofollow noreferrer">XML to JSON Logic App</a></p>
  
  <p>Does anyone have a suggestion for a better approach or a method of allowing larger files to be processed? I have also tried using SSMS, implementing T-SQL BulkInsert, OpenRowSet, etc but I've abandoned this approach for the time being.</p>
  
  <p>Small selection of sources I've used while trying to find a solution:</p>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/18054652/how-can-i-to-read-a-xml-from-a-url-using-t-sql">How can I to read a XML from a URL using T-SQL?</a></li>
  <li><a href="https://stackoverflow.com/questions/45044916/load-data-from-xml-file-to-sql-database-in-azure-using-logic-apps">Load data from XML file to SQL database in Azure using Logic Apps</a></li>
  <li>Countless MVA videos</li>
  </ul>
  
  <p>Many thanks</p>