SQL Server批量导入XML至表：存储与性能优化咨询

我正在SQL Server数据库中读取XML文件，采用INSERT INTO结合BULK的方式，目标是加载指定文件夹内的所有文件。当前实现的存储过程运行时，导致数据库出现严重存储问题。以下是经测试可用的代码：

begin
    
DECLARE @xml XML;
DECLARE @FilePath NVARCHAR(256);
DECLARE @fileList TABLE([FileName] VARCHAR(50), [depth] TINYINT, [isFile] BIT);
DECLARE @filename NVARCHAR(256); 
DECLARE @sql NVARCHAR(MAX);
DECLARE @nome_ficheiro NVARCHAR(256);

DECLARE @V_CARD_LOAD_ID NVARCHAR(256);
DECLARE @V_CARD_serial  NVARCHAR(256);
DECLARE @mes NVARCHAR(256);

DECLARE @mes_seguinte NVARCHAR(256);

DECLARE @ano NVARCHAR(256);
DECLARE @data1 NVARCHAR(256); 
DECLARE @data2 NVARCHAR(256);


DECLARE ListaFiles CURSOR FOR     
SELECT FileName  
FROM @fileList;

  

SET @FilePath = 'e:\test3\'; 

INSERT INTO @fileList 
EXEC xp_dirtree @FilePath, 2, 1
    
SELECT FileName 
FROM @fileList
  
  
--SELECT @filename = f.FileName from @fileList f

OPEN ListaFiles

FETCH NEXT FROM ListaFiles     
        INTO @filename;

            SET @sql = N'SELECT @xml = CAST(BulkColumn AS XML) FROM OPENROWSET(BULK ''' + @FilePath + @filename + ''', SINGLE_BLOB) AS x';
            
            EXEC sp_executesql @sql, N'@xml XML OUTPUT', @xml OUTPUT;
            
            SET @mes = SUBSTRING(@filename,13,2);
           
            SET @mes_seguinte = FORMAT(SUBSTRING(@filename,13,2)+1,'00');
         
            SET @ano = SUBSTRING(@filename,15,4);
           
            SET @data1=@ano+'-'+@mes+'-'+'01 00:00:00';
           
            SET @data2= @ano+'-'+@mes_seguinte+'-'+'01 00:00:00';
          
       DBMS_LOB.CLOSE(
     @FilePath + @filename
    );    
            -- Register the namespace+
            WITH XMLNAMESPACES(
              DEFAULT 'urn:OECD:StandardAuditFile-Tax:PT_1.04_01'
            )
            --/*
            INSERT INTO PayshopInvoiceLines2 WITH (TABLOCK) (
                                InvoiceNo, InvoiceDate, InvoiceType, SourceID, SystemEntryDate, CustomerID,
                                LineNumber, ProductCode, ProductDescription, Description,
                                Quantity, UnitPrice, TaxPercentage, GrossTotal, Nome_Ficheiro, Card_LoadId_Payshop, Data_Carregamento,Estado_Registos 
                            )
                                SELECT
                                Invoice.value('(InvoiceNo)[1]', 'NVARCHAR(50)'),
                                Invoice.value('(InvoiceDate)[1]', 'DATE'),
                                Invoice.value('(InvoiceType)[1]', 'NVARCHAR(10)'),
                                Invoice.value('(SourceID)[1]', 'NVARCHAR(50)'),
                                Invoice.value('(SystemEntryDate)[1]', 'DATETIME'),
                                Invoice.value('(CustomerID)[1]', 'NVARCHAR(50)'),
                                Line.value('(LineNumber)[1]', 'INT'),
                                Line.value('(ProductCode)[1]', 'NVARCHAR(100)'),
                                Line.value('(ProductDescription)[1]', 'NVARCHAR(255)'),
                                Line.value('(Description)[1]', 'NVARCHAR(255)'),
                                Line.value('(Quantity)[1]', 'DECIMAL(18,4)'),
                                Line.value('(UnitPrice)[1]', 'DECIMAL(18,4)'),
                                Line.value('(Tax/TaxPercentage)[1]', 'DECIMAL(5,2)'),
                                Invoice.value('(DocumentTotals/GrossTotal)[1]', 'DECIMAL(18,4)'), 
                                 @FilePath + @filename,' ',CURRENT_TIMESTAMP,' '
                                
                            FROM @xml.nodes('/AuditFile/SourceDocuments/SalesInvoices/Invoice') AS XTbl(Invoice)
                            CROSS APPLY Invoice.nodes('/AuditFile/SourceDocuments/SalesInvoices/Invoice/Line') AS LineTbl(Line);
                        
            CLOSE ListaFiles;
DEALLOCATE ListaFiles;

请问我在实现中存在哪些疏漏？如何调整才能避免对数据库存储及性能造成不良影响？

• 游标未循环遍历所有文件：代码仅执行一次FETCH NEXT，无WHILE循环逻辑，只会处理文件夹中第一个文件，且游标操作存在资源泄漏风险。
• XML节点遍历导致数据爆炸：CROSS APPLY使用绝对路径/AuditFile/SourceDocuments/SalesInvoices/Invoice/Line，会让每个Invoice节点与所有Line节点做笛卡尔积，生成大量重复数据，直接导致存储占用异常飙升。
• 无效的Oracle语法调用：DBMS_LOB.CLOSE是Oracle专属函数，SQL Server无此语法，不仅会报错，还会中断流程。
• XML变量内存占用过高：将整个XML文件加载到@xml变量中，大文件会持续占用大量内存，拖慢数据库性能。
• 文件列表未过滤：xp_dirtree返回结果包含文件夹和文件，未通过isFile=1过滤，可能误处理文件夹。
• 无错误处理机制：单个文件加载或插入失败时，整个存储过程直接中断，无法自动清理游标等资源。

1. 修复游标循环逻辑 #

添加WHILE @@FETCH_STATUS = 0循环，确保遍历所有文件，处理完成后正确推进游标：

OPEN ListaFiles
FETCH NEXT FROM ListaFiles INTO @filename;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 文件处理逻辑
    FETCH NEXT FROM ListaFiles INTO @filename;
END

CLOSE ListaFiles;
DEALLOCATE ListaFiles;

2. 修正XML节点关联逻辑 #

将CROSS APPLY中的绝对路径改为相对路径Line，确保每个Invoice仅关联自身的Line节点，避免笛卡尔积：

FROM @xml.nodes('/AuditFile/SourceDocuments/SalesInvoices/Invoice') AS XTbl(Invoice)
CROSS APPLY Invoice.nodes('Line') AS LineTbl(Line);

3. 移除无效语法并优化变量 #

• 删除DBMS_LOB.CLOSE调用；
• 缩小变量数据类型，比如@mes改为CHAR(2)，@ano改为CHAR(4)，减少内存消耗。

4. 直接读取XML插入，避免内存占用 #

跳过@xml变量，直接通过OPENROWSET关联XML节点插入，减少内存开销：

WITH XMLNAMESPACES(DEFAULT 'urn:OECD:StandardAuditFile-Tax:PT_1.04_01')
INSERT INTO PayshopInvoiceLines2 WITH (TABLOCK) (...)
SELECT
    Invoice.value('(InvoiceNo)[1]', 'NVARCHAR(50)'),
    -- 其他字段...
    @FilePath + @filename,' ',CURRENT_TIMESTAMP,' '
FROM OPENROWSET(BULK ''' + @FilePath + @filename + ''', SINGLE_BLOB) AS x
CROSS APPLY (SELECT CAST(x.BulkColumn AS XML)) AS xmlData(XMLCol)
CROSS APPLY xmlData.XMLCol.nodes('/AuditFile/SourceDocuments/SalesInvoices/Invoice') AS XTbl(Invoice)
CROSS APPLY Invoice.nodes('Line') AS LineTbl(Line);

5. 添加错误处理与资源清理 #

用TRY/CATCH块捕获异常，确保游标能正确关闭和释放：

BEGIN TRY
    -- 游标初始化、文件处理逻辑
END TRY
BEGIN CATCH
    IF CURSOR_STATUS('global','ListaFiles') >= 0
    BEGIN
        CLOSE ListaFiles;
        DEALLOCATE ListaFiles;
    END
    THROW;
END CATCH

6. 过滤有效文件 #

查询文件列表时仅保留文件（isFile=1），避免处理文件夹：

DECLARE ListaFiles CURSOR FOR     
SELECT FileName  
FROM @fileList
WHERE isFile = 1;

修正后的完整代码示例 #

BEGIN
    DECLARE @FilePath NVARCHAR(256);
    DECLARE @fileList TABLE([FileName] VARCHAR(50), [depth] TINYINT, [isFile] BIT);
    DECLARE @filename NVARCHAR(256); 
    DECLARE @sql NVARCHAR(MAX);
    DECLARE @mes CHAR(2);
    DECLARE @mes_seguinte CHAR(2);
    DECLARE @ano CHAR(4);
    DECLARE @data1 NVARCHAR(20); 
    DECLARE @data2 NVARCHAR(20);

    DECLARE ListaFiles CURSOR FOR     
    SELECT FileName  
    FROM @fileList
    WHERE isFile = 1;

    SET @FilePath = 'e:\test3\'; 

    INSERT INTO @fileList 
    EXEC xp_dirtree @FilePath, 2, 1;

    BEGIN TRY
        OPEN ListaFiles
        FETCH NEXT FROM ListaFiles INTO @filename;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @mes = SUBSTRING(@filename,13,2);
            SET @mes_seguinte = FORMAT(CAST(@mes AS INT)+1,'00');
            SET @ano = SUBSTRING(@filename,15,4);
            SET @data1 = @ano+'-'+@mes+'-01 00:00:00';
            SET @data2 = @ano+'-'+@mes_seguinte+'-01 00:00:00';

            -- 直接读取XML并插入，无需存储到变量
            SET @sql = N'
            WITH XMLNAMESPACES(DEFAULT ''urn:OECD:StandardAuditFile-Tax:PT_1.04_01'')
            INSERT INTO PayshopInvoiceLines2 WITH (TABLOCK) (
                                InvoiceNo, InvoiceDate, InvoiceType, SourceID, SystemEntryDate, CustomerID,
                                LineNumber, ProductCode, ProductDescription, Description,
                                Quantity, UnitPrice, TaxPercentage, GrossTotal, Nome_Ficheiro, Card_LoadId_Payshop, Data_Carregamento,Estado_Registos 
                            )
            SELECT
                Invoice.value(''(InvoiceNo)[1]'', ''NVARCHAR(50)''),
                Invoice.value(''(InvoiceDate)[1]'', ''DATE''),
                Invoice.value(''(InvoiceType)[1]'', ''NVARCHAR(10)''),
                Invoice.value(''(SourceID)[1]'', ''NVARCHAR(50)''),
                Invoice.value(''(SystemEntryDate)[1]'', ''DATETIME''),
                Invoice.value(''(CustomerID)[1]'', ''NVARCHAR(50)''),
                Line.value(''(LineNumber)[1]'', ''INT''),
                Line.value(''(ProductCode)[1]'', ''NVARCHAR(100)''),
                Line.value(''(ProductDescription)[1]'', ''NVARCHAR(255)''),
                Line.value(''(Description)[1]'', ''NVARCHAR(255)''),
                Line.value(''(Quantity)[1]'', ''DECIMAL(18,4)''),
                Line.value(''(UnitPrice)[1]'', ''DECIMAL(18,4)''),
                Line.value(''(Tax/TaxPercentage)[1]'', ''DECIMAL(5,2)''),
                Invoice.value(''(DocumentTotals/GrossTotal)[1]'', ''DECIMAL(18,4)''), 
                ''' + @FilePath + @filename + ''','''',CURRENT_TIMESTAMP,''''
            FROM OPENROWSET(BULK ''' + @FilePath + @filename + ''', SINGLE_BLOB) AS x
            CROSS APPLY (SELECT CAST(x.BulkColumn AS XML)) AS xmlData(XMLCol)
            CROSS APPLY xmlData.XMLCol.nodes(''/AuditFile/SourceDocuments/SalesInvoices/Invoice'') AS XTbl(Invoice)
            CROSS APPLY Invoice.nodes(''Line'') AS LineTbl(Line);';

            EXEC sp_executesql @sql;

            FETCH NEXT FROM ListaFiles INTO @filename;
        END

        CLOSE ListaFiles;
        DEALLOCATE ListaFiles;
    END TRY
    BEGIN CATCH
        IF CURSOR_STATUS('global','ListaFiles') >= 0
        BEGIN
            CLOSE ListaFiles;
            DEALLOCATE ListaFiles;
        END
        THROW;
    END CATCH
END

内容的提问来源于stack exchange，提问作者Nelson Soares