Sql-Server

將 XML 文件導入 SQL Server 2012

  • October 7, 2015

每個星期五我都必須將幾個（有時超過 300 個）XML 文件導入到 2 個表中。

其中一張表 的結構R000000如下所示：

R00000010 | R00000020 | R00000030 | R00000040 | R00000050 | R00000060 
---------- ------------ ---------- ----------- ----------- ----------
R000000   |     I     |   0002    |     1     |     2     |    0026
R000000   |     I     |   0003    |     1     |     2     |    0025
R000000   |     I     |   0004    |     1     |     2     |    0021
R000000   |     I     |   0006    |     1     |     2     |    0023
R000000   |     I     |   0001    |     1     |     2     |    0022

^ 每行對應一個 XML 文件。

結構沒有改變，只有數據（在這種情況下，我已經放置了一些隨機數據，例如）。

XML 文件如下所示：

<?xml version="1.0" encoding="ISO-8859-1"?>
<ns0:P4131 xmlns:ns0="http://switching/xi">
<R000000>
   <R00000010>R000000</R00000010>
   <R00000020>I</R00000020>
   <R00000030>0002</R00000030>
   <R00000040>1</R00000040>
   <R00000050>0026</R00000050>
   <R00000060>2</R00000060>
</R000000>
</ns0:P4131>

做這個的最好方式是什麼？我目前正在 Access 中執行此操作。

試試下面的東西……

您顯然需要為您的環境插入變數，檢查數據類型（可能需要添加邏輯以保持前導零？），從最終的臨時表更改為正常表等。

對我來說可以很好地從 XML 文件導入臨時表而不刪除文件，但是使用另一個 xp_cmdshell 命令添加邏輯以從 UNC 路徑中刪除文件應該不會太困難。

DECLARE @folder AS VARCHAR(1000) = '\\servername\sharename\folder\subfolder1\'
DECLARE @command VARCHAR(500) = 'DIR /B "' + @folder + '*.xml"'
DECLARE @file VARCHAR(100)
DECLARE @filesinafolder TABLE (filenameswithfolder VARCHAR(500))
DECLARE @sql NVARCHAR(4000)

-- create global temp table
IF OBJECT_ID('tempdb..##XMLImport') IS NOT NULL
   DROP TABLE ##XMLImport

CREATE TABLE ##XMLImport (
   R00000010 VARCHAR(7)
   ,R00000020 VARCHAR(1)
   ,R00000030 INT
   ,R00000040 INT
   ,R00000050 INT
   ,R00000060 INT
   )

INSERT INTO @filesinafolder
EXEC master..xp_cmdshell @command

-- create cursor
DECLARE filecurs CURSOR
FOR
SELECT REPLACE(filenameswithfolder, @folder, '') AS filenames
FROM @filesinafolder
WHERE filenameswithfolder IS NOT NULL

OPEN filecurs

FETCH NEXT
FROM filecurs
INTO @file

IF @file = 'FILE NOT FOUND'
   GOTO exitprocessing

WHILE @@fetch_status != - 1
BEGIN
   SET @sql = 'DECLARE @X XML

                   SELECT @X = P
                   FROM OPENROWSET(BULK ''' + @folder + '' + @file + ''', SINGLE_BLOB) AS Products(P)

                   DECLARE @iX INT

                   EXEC sp_xml_preparedocument @iX OUTPUT
                       ,@X

                   SELECT *
                   INTO #XMLResults
                   FROM OPENXML(@iX, ''/*/*'', 2) WITH (
                           R00000010 VARCHAR(7)
                           ,R00000020 VARCHAR(1)
                           ,R00000030 INT
                           ,R00000040 INT
                           ,R00000050 INT
                           ,R00000060 INT
                           )
                   EXEC sp_xml_removedocument @iX

                   INSERT INTO ##XMLImport
                   SELECT R00000010
                       ,R00000020
                       ,R00000030
                       ,R00000040
                       ,R00000050
                       ,R00000060
                   FROM #XMLResults'

   --PRINT @sql

   EXEC sp_executesql @sql

   -- process next file
   FETCH NEXT
   FROM filecurs
   INTO @file
END

exitprocessing:

-- clean up
CLOSE filecurs

DEALLOCATE filecurs

SELECT *
FROM ##XMLImport

這實際上通過 SQLCLR 非常簡單。可以將儲存過程設置為讀取xml特定目錄中的任何文件（或輕鬆檢查所有子目錄）並輸出單個結果集及其所有內容。這樣做，您可以使用以下查詢填充您的表：

INSERT INTO dbo.R000000 (R00000010, R00000020, R00000030, R00000040, R00000050, R00000060)
   EXEC dbo.GetXmlDataFromFiles(N'C:\Path\To\XML\Files');

就是這樣。

以下程式碼將讀取輸入參數.xml指定的目錄中的任何文件@FilePath，可選擇遍歷子目錄，並返回每個文件內容的單個結果集。

請注意：

  • 該程式碼假定每個文件只有一個節點，因為問題聲明“每一行對應一個 XML 文件”。並且範例數據與該陳述一致。
  • 如果任何文件可能有多個<R000000>節點，那麼很容易更改此程式碼來處理它。
  • 每個文件的內容在讀取後立即作為結果行返回。這意味著一次只有一個文件在記憶體中，而不是讀取所有文件並在完成後發回整個結果集。因此，這可以很好地擴展，如果您導入 3000 個文件也沒關係。
using System;
using System.Data;
using System.Data.SqlTypes;
using System.IO;
using System.Xml;
using Microsoft.SqlServer.Server;

public class ImportXmlFiles
{
 [Microsoft.SqlServer.Server.SqlProcedure]
 public static void ReadXmlFiles([SqlFacet(MaxSize = 500)] SqlString FilePath,
    SqlBoolean Recursive)
 {
   XmlDocument _FileContents = new XmlDocument();

   SqlDataRecord _ResultRow = new SqlDataRecord(new SqlMetaData[]{
      new SqlMetaData("R00000010", SqlDbType.VarChar, 10),
      new SqlMetaData("R00000020", SqlDbType.VarChar, 10),
      new SqlMetaData("R00000030", SqlDbType.VarChar, 10),
      new SqlMetaData("R00000040", SqlDbType.Int),
      new SqlMetaData("R00000050", SqlDbType.VarChar, 10),
      new SqlMetaData("R00000060", SqlDbType.Int)
   });

   SqlContext.Pipe.SendResultsStart(_ResultRow);

   foreach (string _FileName in Directory.GetFiles(FilePath.Value, "*.xml",
      (Recursive.IsTrue) ? SearchOption.AllDirectories : SearchOption.TopDirectoryOnly)
           )
   {
     _FileContents.Load(_FileName);
     XmlElement _Row = (XmlElement)_FileContents.SelectSingleNode("//R000000");

     _ResultRow.SetString(0, _Row.SelectSingleNode("./R00000010").InnerText);
     _ResultRow.SetString(1, _Row.SelectSingleNode("./R00000020").InnerText);
     _ResultRow.SetString(2, _Row.SelectSingleNode("./R00000030").InnerText);
     _ResultRow.SetInt32(3,
        Convert.ToInt32(_Row.SelectSingleNode("./R00000040").InnerText));
     _ResultRow.SetString(4, _Row.SelectSingleNode("./R00000050").InnerText);
     _ResultRow.SetInt32(5,
        Convert.ToInt32(_Row.SelectSingleNode("./R00000060").InnerText));

     SqlContext.Pipe.SendResultsRow(_ResultRow);
   }

   SqlContext.Pipe.SendResultsEnd();

   return;
 }
}

上面顯示的 SQLCLR 儲存過程的一個易於安裝的工作範例可在 Pastebin 上找到：

SQLCLR 儲存過程返回一個包含許多 XML 文件的結果集

！！請注意，雖然 Assembly 設置為，但EXTERNAL_ACCESS的數據庫屬性TRUSTWORTHY未設置為，正如您可以在網際網路上找到的大多數 SQLCLR 範例中所做的那樣。程序集在編譯時被簽名（給定一個強名稱），因此安裝腳本創建一個非對稱密鑰 in ，一個基於該非對稱密鑰的登錄，然後授予該登錄權限。這不僅允許在不需要的情況下將 Assembly 設置為，而且還不允許將 Assembly 設置為，如果設置為!!ON``[master]``EXTERNAL ACCESS ASSEMBLY``EXTERNAL_ACCESS``TRUSTWORTHY ON``UNSAFE``TRUSTWORTHY``ON

另一種更通用並允許導入各種 XML 結構的方法是使用表值函式而不是儲存過程。它甚至比此處顯示的儲存過程更容易讀取每個文件的內容，並為結果集中的每個文件返回 1 行，該結果集中是 XML 數據類型的 1 個欄位。然後，您可以根據需要使用 T-SQL.nodes()和.value()函式來解析出不同的結構。

引用自：https://dba.stackexchange.com/questions/117005

相關問答

Sql-Server

裝配中功能的執行時間/成本

  • November 29, 2021
檢視問答
Sql-Server

如何將 NetCDF 文件中的數據導入 SQL Server？

  • June 10, 2020
檢視問答
Sql-Server

使用 XML 閱讀器優化計劃

  • November 8, 2019
檢視問答
Sql-Server

在 XML 列中編輯單個條目的最簡單方法？

  • October 25, 2019
檢視問答
Sql-Server

當 where 子句過濾 value() 時，為什麼不使用二級選擇性索引？

  • October 23, 2019
檢視問答
Sql-Server

如何遍歷所有表行並使用同一行的值執行子查詢？

  • September 3, 2019
檢視問答