正在将(大)XML数据加载到Azure SQL数据库表中

Question

我一直在研究一个将大型XML文件传输到Azure SQL数据库的解决方案，但迄今为止没有成功。XML文件是通过一个固定的URL访问的。在这个项目之前，我没有使用SQL或Azure的经验，所以如果我错过了一些明显的东西，我很抱歉。

我用合适的表设置了数据库，我只需要用我的数据填充这些表。

XML文件有以下格式，但是有几千个作业。下载时，该文件大约为1.2Mb，但我想从URL而不是本地计算机访问它。

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

到目前为止，我最成功的方法是使用一些内置的Azure Logic应用程序。我使用HTMLGET加载XML数据，然后将其传递到Liquid应用程序中，该应用程序将XML转换为JSON。然后我希望JSON格式可以很容易地加载到数据库表中。这个逻辑应用程序成功地将XML转换为JSON，但只有当我将文件剥离到大约80个作业时。因此，我知道我的逻辑应用程序可以工作，文件大小是我的问题。

XML到JSON逻辑应用

有人建议采取更好的方法或方法来处理更大的文件吗？我也尝试过使用SSMS、实现T-SQL BulkInsert、OpenRowSet等，但我暂时放弃了这种方法。

我在尝试寻找解决方案时使用的少量来源：

• 如何使用T-SQL从URL读取XML
• 使用Logic Apps将数据从XML文件加载到Azure中的SQL数据库
• 无数MVA视频

非常感谢

Answer 1

我没有如何通过Azure SQL Server从URL加载XML的经验。在normalSQL Server中，有一些奇怪的方法，但我建议您从另一个应用程序中读取URL
但是，由于您设法加载了文件，所以这似乎不是您的问题。

为什么您觉得有必要将其转换为JSON？SQL Server可以很好地处理本机XML。标准传输格式是NVARCHAR(MAX)，它是一个UCS-2-字符串，与UTF-16或简单的2-byte-unicode几乎相同。任何.Net字符串都可以按原样发送到SQL Server。NVARCHAR(MAX)的大小限制足够满足您的需求。。。

只需创建一个如下所示的函数，并通过您的阅读应用程序调用它。您可以将参数作为字符串传递：

此函数将接受2-byte_encodedunicode字符串，并将其隐式转换为XML：

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

--假设您已经将XML加载到一个字符串中，您可以这样测试它：

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

--使用函数的方式与使用表格的方式完全相同：

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;

应将结果插入到临时表中。对这个暂存表执行任何需要的清理和业务逻辑，然后从那里开始。

结果

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