Loading (large) XML data into Azure SQL database table

I have no experience on how do I load an XML from an URL via Azure SQL Server. In normal SQL-Server there are some weird approaches, but I'd recommend do read the URL from another application.
But - as you managed to load the file somehow - this does not seem to be your issue anyway.

Why do you feel the need to convert this to JSON? SQL-Server can deal with native XML pretty well. The standard transfer format is NVARCHAR(MAX), which is an UCS-2-string, which is almost the same as UTF-16 or simple 2-byte-unicode. Any .Net-string can be sent to SQL-Server as is. The size limit of NVARCHAR(MAX) is big enough for your needs...

Just create a function like the following and call it via your reading application. You can pass the parameter as a string:

This function will accept a 2-byte_encoded unicode string and convert this to XML implicitly:

CREATE FUNCTION dbo.ReadTheXml(@xml XML)
RETURNS TABLE
AS
RETURN
    SELECT @xml.value('(/Response/Status/text())[1]','nvarchar(max)') AS Response_Status
          ,job.value('(ID/text())[1]','nvarchar(max)') AS Job_ID
          ,job.value('(Name/text())[1]','nvarchar(max)') AS Job_Name
          ,job.value('(Description/text())[1]','nvarchar(max)') AS Job_Description
          ,job.value('(StartDate/text())[1]','datetime') AS Job_StartDate
          ,job.value('(DueDate/text())[1]','datetime') AS Job_DueDate
          ,job.value('(Manager/ID/text())[1]','int') AS Job_Manager_ID
          ,job.value('(Manager/Name/text())[1]','nvarchar(max)') AS Job_Manager_Name
    FROM @xml.nodes('/Response/Jobs/Job') A(job)
GO

--Assuming you've got the XML loaded into a string already you can test it like this:

DECLARE @xml NVARCHAR(MAX) =         --the xml as NVARCHAR(MAX) string
N'<Response api-method="Current">
  <Status>OK</Status>
     <Jobs>
        <Job>
           <ID>N1234</ID>
           <Name>Job name here</Name>
           <Description>Job description here</Description>
           <StartDate>2018-10-08T00:00:00</StartDate>
           <DueDate>2018-10-21T00:00:00</DueDate>
           <Manager>
              <ID>12345</ID>
              <Name>John Smith</Name>
           </Manager>
        </Job>
        <Job>
           <ID>blah</ID>
           <Name>One more</Name>
           <Description>This is one more description</Description>
           <StartDate>2018-10-08T00:00:00</StartDate>
           <DueDate>2018-10-21T00:00:00</DueDate>
           <Manager>
              <ID>12345</ID>
              <Name>John Smith</Name>
           </Manager>
        </Job>
     </Jobs>
</Response>';

--Use the function exactly the same way as you'd use a table:

SELECT * INTO #tmpStagingTable 
FROM dbo.ReadTheXml(@xml); --pass in the XML as string

--call the result from the staging table
SELECT * FROM #tmpStagingTable;  --create a staging table *on the fly*
GO

--clean up for testing
DROP FUNCTION dbo.ReadTheXml;
DROP TABLE #tmpStagingTable;

The result should be inserted into a staging table. Do any needed cleansing and business logic against this staging table and proceed from there.

The result

+-----------------+--------+---------------+------------------------------+-------------------------+-------------------------+----------------+------------------+
| Response_Status | Job_ID | Job_Name      | Job_Description              | Job_StartDate           | Job_DueDate             | Job_Manager_ID | Job_Manager_Name |
+-----------------+--------+---------------+------------------------------+-------------------------+-------------------------+----------------+------------------+
| OK              | N1234  | Job name here | Job description here         | 2018-10-08 00:00:00.000 | 2018-10-21 00:00:00.000 | 12345          | John Smith       |
+-----------------+--------+---------------+------------------------------+-------------------------+-------------------------+----------------+------------------+
| OK              | blah   | One more      | This is one more description | 2018-10-08 00:00:00.000 | 2018-10-21 00:00:00.000 | 12345          | John Smith       |
+-----------------+--------+---------------+------------------------------+-------------------------+-------------------------+----------------+------------------+