将日常 XML 文件自动处理到 SQL 数据库中的最佳方式-解网

问：

我需要自动处理频繁流入 SQL Server 数据库的 XML 文件。我有一个示例XML文件，并且已经能够通过使用SQL查询处理数据。OPENROWSET

但是，这仅适用于手动，因为我被告知不允许使用变量来定义要导入的文件的名称/路径。OPENROWSET

代码如下：

WITH rs (xmlData) AS
(
   SELECT TRY_CAST(BulkColumn AS XML) 
   FROM OPENROWSET(BULK N'C:\Users\SFLYNN01\Desktop\jobStatusUpdate-C158788-20231017161702.xml', SINGLE_BLOB) AS x
)
SELECT 
      c.value('(JobRef/text())[1]', 'VARCHAR(20)')AS JobRef
    , c.value('(Stages[Type/text()="1"]/Address/AddressCode/text())[1]', 'VARCHAR(20)')AS ColCode
    , c.value('(Stages[Type/text()="1"]/DateTimeFrom/text())[1]', 'DATE') AS ColDate
    , c.value('(Stages[Type/text()="1"]/DateTimeFrom/text())[1]', 'TIME') AS ColTime
    , c.value('(Stages[Type/text()="2"]/Address/AddressCode/text())[1]', 'VARCHAR(20)')AS DelCode
    , c.value('(Stages[Type/text()="2"]/DateTimeFrom/text())[1]', 'DATE') AS DelDate
    , c.value('(Stages[Type/text()="2"]/DateTimeFrom/text())[1]', 'TIME') AS DelTime
    , c.value('(Response/JobTrackingURL/text())[1]', 'VARCHAR(20)')AS TrackingURL
    , c.value('(statusId/text())[1]', 'VARCHAR(20)')AS Status
FROM 
    rs
CROSS APPLY 
    xmldata.nodes('/job') AS t(c)

谁能建议一种方法，让我可以在数据库中的 XML 文件放入等待的 SFTP 文件夹时自动处理它们？对于手动过程来说，文件量太大了。谢谢。

SQL-Server XML OpenRowSet

$filePath = "`'C:\Temp\print4.pdf`'"
$select = "
WITH rs (xmlData) AS
    (SELECT TRY_CAST(BulkColumn AS XML) 
   FROM OPENROWSET(BULK Nxpathx, SINGLE_BLOB) AS x)
SELECT 
     c.value('(JobRef/text())[1]', 'VARCHAR(20)')AS JobRef
   , c.value('(Stages[Type/text()=`"1`"]/Address/AddressCode/text())[1]', 'VARCHAR(20)')AS ColCode
   , c.value('(Stages[Type/text()=`"1`"]/DateTimeFrom/text())[1]', 'DATE') AS ColDate
   , c.value('(Stages[Type/text()=`"1`"]/DateTimeFrom/text())[1]', 'TIME') AS ColTime
   , c.value('(Stages[Type/text()=`"2`"]/Address/AddressCode/text())[1]', 'VARCHAR(20)')AS DelCode
   , c.value('(Stages[Type/text()=`"2`"]/DateTimeFrom/text())[1]', 'DATE') AS DelDate
   , c.value('(Stages[Type/text()=`"2`"]/DateTimeFrom/text())[1]', 'TIME') AS DelTime
   , c.value('(Response/JobTrackingURL/text())[1]', 'VARCHAR(20)')AS TrackingURL
   , c.value('(statusId/text())[1]', 'VARCHAR(20)')AS Status
FROM rs
CROSS APPLY xmldata.nodes('/job') AS t(c)"

$select = $select -replace 'xpathx', $filePath

然后，您可以使用发送$select。由于您没有编写您使用的数据库，因此我无法从上面的例子中说如何继续。但是 PowerShell 具有适用于许多不同数据库的插件。

declare 
    @SQL nvarchar(max),
    @XML xml,
    @FileName nvarchar(1000) ='C:\Users\SFLYNN01\Desktop\jobStatusUpdate-C158788-20231017161702.xml'

select @SQL = N'
   SELECT @XML = TRY_CAST(BulkColumn AS XML) 
   FROM OPENROWSET(BULK ', quotename(@FileName, ''''), ' SINGLE_BLOB) AS x'

exec sp_executesql
    @SQL,
    N'@XML output',
    @Xml output

SELECT 
...
FROM @Xml.nodes('/job') AS t(c)

之后，您可以像往常一样进行解析，只是针对变量。请注意，您也可以像最初一样使用 CTE 在动态 sql 中进行所有解析，但这有很多转义的单引号，没有人有时间。

就摄取 XML 文件的完整过程而言，您需要某种方式从 SFTP 获取文件。这要么通过轮询 SFTP 来完成，要么如果您足够幸运地从中获得一些通知，请根据它采取行动。这将在很大程度上取决于你拥有哪些工具和专业知识来构建流程。Airflow 将是一个很好的通用选项，但您可能也可以很容易地编写运行 powershell 脚本的代理作业来执行相同的操作。

上一个：从 XML 中获取描述符的值

下一个：如何使此 xml 查询更快

将日常 XML 文件自动处理到 SQL 数据库中的最佳方式

Best way to automate processing of daily XML files into SQL database

评论

评论