如何使用動態t-sql決議檔案夾中的XML檔案？

我想將此 XML 更改為下面的 SQL 陳述句以使用路徑而不是檔案名，目前它正在尋找“C:\Test\XML\PT38.xml”。

我需要它來決議檔案夾中的任何 .XML 檔案，而不是尋找特定的檔案。一次只有一個檔案，但它們會有不同的名稱（數量增加：PT39、PT40 等）。

我嘗試為路徑添加一個變數，然后更改 BULK 以查找該變數，但它按預期失敗。

我已經閱讀了有關創建臨時表然后決議日期的內容，但我不確定這對我有用。

我會很感激幫助。

這是我嘗試過的：

DECLARE @xmlFileName varchar(100) = 'C:\Test\XML\'

FROM OPENROWSET(BULK '''   @xmlFileName   ''', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)

這是 XML 內容：

<?xml version="1.0" encoding="UTF-8" ?>
<MOD1>
   <DC BEGIN="1">
      <DC4 SEGMENT="1">
         <TABNAM>DC4</TABNAM>
         <DOCNUM>0000003899888135</DOCNUM>
      </DC4>
      <ZPR SEGMENT="1">
         <AUFNR>000915229446</AUFNR>
         <LNO>RM01PL01</LNO>
         <CHARG>0006186588</CHARG>
         <STR2>211609</STR2>
         <QTY>4166.000</QTY>
         <PLN_ORDER>6963701111</PLN_ORDER>
      </ZPR>
   </DC>
</MOD1>

這是 SQL 表：

CREATE TABLE XMLTESTTABLE
(
    PON int, 
    ASP int, 
    LTN varchar(11),   
    GAS int, 
    QY varchar(15), 
    LNO varchar(2), 
    StartTime date,
);

這是宣告：

INSERT INTO XMLTESTTABLE(PON, ASP, LTN, GAS, QY, LNO, StartTime)
SELECT ZPRM.value('(AUFNR/text())[1]', 'int')
    , ZPRM.value('(CHARG/text())[1]', 'int')
    , ZPRM.value('(PLN_ORDER/text())[1]', 'VARCHAR(10)')
    , ZPRM.value('(CHARG/text())[1]', 'int')
    , ZPRM.value('(QTY/text())[1]', 'DECIMAL(10,0)') AS [qty]
    , RIGHT(ZPRM.value('(LNO/text())[1]', 'VARCHAR(10)'), 2) AS [LNO]
    , TRY_CAST(STUFF(STUFF(ZPRM.value('(STR2/text())[1]', 'CHAR(6)'),3,0,':'),6,0,':') AS TIME)
FROM (SELECT TRY_CAST(MY_XML AS xml)
      FROM OPENROWSET(BULK 'C:\Test\XML\PT38.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
      CROSS APPLY MY_XML.nodes('/MOD1/DC/ZPR') AS MY_XML(ZPRM);

uj5u.com熱心網友回復：

在 SQL Server 2017 及更高版本中實作起來要容易得多。它有更好的 API 來處理檔案系統。

請嘗試以下解決方案。它將在 SQL Server 2012 中運行。

我將StartTime列資料型別修改為TIME(0).

您需要修改@folder變數值以匹配您在環境中的內容。

SQL

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.XMLTESTTABLE;

CREATE TABLE dbo.XMLTESTTABLE
(
    PON varchar(10), 
    ASP int, 
    LTN varchar(11),   
    GAS int, 
    QY varchar(15), 
    LNO varchar(2), 
    StartTime TIME(0)
);

DECLARE @xml XML
   , @sql NVARCHAR(MAX)
   , @XMLfileName VARCHAR(256) -- 'e:\Temp\TradeFeed\PT38.xml';
   , @folder VARCHAR(256) = 'e:\Temp\TradeFeed';

DECLARE @tbl TABLE (
    id INT IDENTITY(1,1) PRIMARY KEY,
    [fileName] VARCHAR(512),
    depth INT,
    isfile BIT
);

INSERT INTO @tbl ([fileName], depth, isfile)
EXEC master.sys.xp_dirtree @folder,1,1;

-- just to see
SELECT * FROM @tbl;

-- filter out not need files
SELECT TOP(1) @XMLfileName = CONCAT(@folder, '\', [fileName])
FROM @tbl
WHERE isfile = 1
    AND [fileName] LIKE '%.xml';


SET @sql = N'SELECT @xmlOut = XmlDoc FROM OPENROWSET (BULK '   QUOTENAME(@XMLfileName,NCHAR(39))   ', SINGLE_BLOB) AS Tab(XmlDoc)';

EXEC master.sys.sp_executesql @sql, N'@xmlOut XML OUTPUT', @xmlOut = @xml OUTPUT;

INSERT INTO XMLTESTTABLE(PON, ASP, LTN, GAS, QY, LNO, StartTime)
SELECT @xml.value('(/MOD1/DC/DC4/TABNAM/text())[1]', 'VARCHAR(10)')
    , c.value('(CHARG/text())[1]', 'int')
    , c.value('(PLN_ORDER/text())[1]', 'VARCHAR(10)')
    , c.value('(CHARG/text())[1]', 'int')
    , c.value('(QTY/text())[1]', 'DECIMAL(10,0)') AS [qty]
    , RIGHT(c.value('(LNO/text())[1]', 'VARCHAR(10)'), 2) AS [LNO]
    , TRY_CAST(STUFF(STUFF(c.value('(STR2/text())[1]', 'CHAR(6)'),3,0,':'),6,0,':') AS TIME(0))
FROM @xml.nodes('/MOD1/DC/ZPR') AS t(c);

-- test
SELECT * FROM dbo.XMLTESTTABLE;

輸出

 ----------- --------- ------------ --------- ------ ----- ----------- 
|    PON    |   ASP   |    LTN     |   GAS   |  QY  | LNO | StartTime |
 ----------- --------- ------------ --------- ------ ----- ----------- 
|    DC4    | 6186588 | 6963701111 | 6186588 | 4166 |  01 | 21:16:09  |
 ----------- --------- ------------ --------- ------ ----- ----------- 

標籤：sql sql服务器 xml 查询 动态 sql

上一篇：如何為兩個xml檔案添加合并模板？

下一篇：陣列大小的彈性搜索聚合