Loading (large) XML data into Azure SQL database table

1

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.

I setup the database with appropriate tables, I just need to populate the tables with my data.

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.

<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>

            Repeat above several thousand times

        </Job>

     </Jobs>

</Response>

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.

XML to JSON Logic App

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.

Small selection of sources I've used while trying to find a solution:

• How can I to read a XML from a URL using T-SQL?


• Load data from XML file to SQL database in Azure using Logic Apps


• Countless MVA videos

Many thanks

xml tsql xml-parsing azure-sql-database azure-logic-apps

share|improve this question

asked Nov 22 '18 at 4:37

MitchWMitchW

83

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  you should have a look at azure data factory, it will be a better fit for what youve explained
  
  – Thomas
  Nov 23 '18 at 8:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks Thomas, I was planning on using data factory once the data was in JSON format since data factory cannot handle XML as far as I could tell.
  
  – MitchW
  Nov 26 '18 at 4:06
  

  
  
  
  

add a comment | 

1

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.

I setup the database with appropriate tables, I just need to populate the tables with my data.

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.

<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>

            Repeat above several thousand times

        </Job>

     </Jobs>

</Response>

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.

XML to JSON Logic App

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.

Small selection of sources I've used while trying to find a solution:

• How can I to read a XML from a URL using T-SQL?


• Load data from XML file to SQL database in Azure using Logic Apps


• Countless MVA videos

Many thanks

xml tsql xml-parsing azure-sql-database azure-logic-apps

share|improve this question

asked Nov 22 '18 at 4:37

MitchWMitchW

83

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  you should have a look at azure data factory, it will be a better fit for what youve explained
  
  – Thomas
  Nov 23 '18 at 8:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks Thomas, I was planning on using data factory once the data was in JSON format since data factory cannot handle XML as far as I could tell.
  
  – MitchW
  Nov 26 '18 at 4:06
  

  
  
  
  

add a comment | 

1

1

1

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.

I setup the database with appropriate tables, I just need to populate the tables with my data.

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.

<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>

            Repeat above several thousand times

        </Job>

     </Jobs>

</Response>

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.

XML to JSON Logic App

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.

Small selection of sources I've used while trying to find a solution:

• How can I to read a XML from a URL using T-SQL?


• Load data from XML file to SQL database in Azure using Logic Apps


• Countless MVA videos

Many thanks

xml tsql xml-parsing azure-sql-database azure-logic-apps

share|improve this question

asked Nov 22 '18 at 4:37

MitchWMitchW

83

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.

I setup the database with appropriate tables, I just need to populate the tables with my data.

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.

<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>

            Repeat above several thousand times

        </Job>

     </Jobs>

</Response>

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.

XML to JSON Logic App

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.

Small selection of sources I've used while trying to find a solution:

• How can I to read a XML from a URL using T-SQL?


• Load data from XML file to SQL database in Azure using Logic Apps


• Countless MVA videos

Many thanks

xml tsql xml-parsing azure-sql-database azure-logic-apps

xml tsql xml-parsing azure-sql-database azure-logic-apps

share|improve this question

asked Nov 22 '18 at 4:37

MitchWMitchW

83

share|improve this question

asked Nov 22 '18 at 4:37

MitchWMitchW

83

share|improve this question

share|improve this question

asked Nov 22 '18 at 4:37

MitchWMitchW

83

asked Nov 22 '18 at 4:37

MitchWMitchW

83

asked Nov 22 '18 at 4:37

MitchWMitchW

83

83

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  you should have a look at azure data factory, it will be a better fit for what youve explained
  
  – Thomas
  Nov 23 '18 at 8:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks Thomas, I was planning on using data factory once the data was in JSON format since data factory cannot handle XML as far as I could tell.
  
  – MitchW
  Nov 26 '18 at 4:06
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  you should have a look at azure data factory, it will be a better fit for what youve explained
  
  – Thomas
  Nov 23 '18 at 8:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks Thomas, I was planning on using data factory once the data was in JSON format since data factory cannot handle XML as far as I could tell.
  
  – MitchW
  Nov 26 '18 at 4:06
  

  
  
  
  

you should have a look at azure data factory, it will be a better fit for what youve explained

– Thomas
Nov 23 '18 at 8:30

you should have a look at azure data factory, it will be a better fit for what youve explained

– Thomas
Nov 23 '18 at 8:30

Thanks Thomas, I was planning on using data factory once the data was in JSON format since data factory cannot handle XML as far as I could tell.

– MitchW
Nov 26 '18 at 4:06

Thanks Thomas, I was planning on using data factory once the data was in JSON format since data factory cannot handle XML as far as I could tell.

– MitchW
Nov 26 '18 at 4:06

add a comment | 

1 Answer
1

active

oldest

votes

2

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       |

+-----------------+--------+---------------+------------------------------+-------------------------+-------------------------+----------------+------------------+

share|improve this answer

answered Nov 22 '18 at 7:39

ShnugoShnugo

50k72669

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you Shnugo, this is exactly what I needed. Simple yet effective, appreciate your help.
  
  – MitchW
  Nov 26 '18 at 4:11
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53423970%2floading-large-xml-data-into-azure-sql-database-table%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

active

oldest

votes

active

oldest

votes

2

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       |

+-----------------+--------+---------------+------------------------------+-------------------------+-------------------------+----------------+------------------+

share|improve this answer

answered Nov 22 '18 at 7:39

ShnugoShnugo

50k72669

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you Shnugo, this is exactly what I needed. Simple yet effective, appreciate your help.
  
  – MitchW
  Nov 26 '18 at 4:11
  

  
  
  
  

add a comment | 

2

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       |

+-----------------+--------+---------------+------------------------------+-------------------------+-------------------------+----------------+------------------+

share|improve this answer

answered Nov 22 '18 at 7:39

ShnugoShnugo

50k72669

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you Shnugo, this is exactly what I needed. Simple yet effective, appreciate your help.
  
  – MitchW
  Nov 26 '18 at 4:11
  

  
  
  
  

add a comment | 

2

2

2

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       |

+-----------------+--------+---------------+------------------------------+-------------------------+-------------------------+----------------+------------------+

share|improve this answer

answered Nov 22 '18 at 7:39

ShnugoShnugo

50k72669

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       |

+-----------------+--------+---------------+------------------------------+-------------------------+-------------------------+----------------+------------------+

share|improve this answer

answered Nov 22 '18 at 7:39

ShnugoShnugo

50k72669

share|improve this answer

share|improve this answer

answered Nov 22 '18 at 7:39

ShnugoShnugo

50k72669

answered Nov 22 '18 at 7:39

ShnugoShnugo

50k72669

answered Nov 22 '18 at 7:39

ShnugoShnugo

50k72669

50k72669

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you Shnugo, this is exactly what I needed. Simple yet effective, appreciate your help.
  
  – MitchW
  Nov 26 '18 at 4:11
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you Shnugo, this is exactly what I needed. Simple yet effective, appreciate your help.
  
  – MitchW
  Nov 26 '18 at 4:11
  

  
  
  
  

Thank you Shnugo, this is exactly what I needed. Simple yet effective, appreciate your help.

– MitchW
Nov 26 '18 at 4:11

Thank you Shnugo, this is exactly what I needed. Simple yet effective, appreciate your help.

– MitchW
Nov 26 '18 at 4:11

add a comment | 

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53423970%2floading-large-xml-data-into-azure-sql-database-table%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

This page is only for reference, If you need detailed information, please check here