大量将巨大的XML导入SQL Cellt

发布于 2025-02-09 08:43:16 字数 1161 浏览 1 评论 0原文

我正在尝试将XML文件导入SQL单元格进行处理。我的第一个想法是做一个Openrowset，以保持XML，并用节点将其分开。其中一个XML太大而无法将其保存在单元格上，因此Openrowset切断了XML，因此不可能使用它。那是代码：

    DECLARE  @XMLwithOpenXML TABLE
    (
        Id              INT IDENTITY PRIMARY KEY,
        XMLData         XML,
        LoadedDateTime  DATETIME
    )

    INSERT INTO @XMLwithOpenXML(XMLData, LoadedDateTime)
    SELECT      CONVERT(XML, BulkColumn) AS BulkColumn
                ,GETDATE() 
    FROM OPENROWSET(BULK 'C:\temp\PP015.xml', SINGLE_CLOB) AS x;
    
    SELECT * FROM @XMLwithOpenXML

第二个选项是使用BCP执行相同的操作，但是我会遇到错误。

    DECLARE @sql NVARCHAR(500) 

    SET @sql = 'bcp [ExternaDB].[dbo].[xmltab] IN "C:\temp\PP015.xml" -T -c'
    EXEC xp_cmdshell  @sql

    select * from xmltab

我想知道我是否采用正确的方式（如何使用XML在SQL单元格中我知道如何做）以及如何批量导入完整的XML进入没有长度约束的单元格。

原文

I'm trying to import an XML file into a SQL cell to process it. My first idea is do an OPENROWSET to keep the XML and the just divide it with NODES. One of the XML its too huge to keep it on a CELL, so the OPENROWSET cut the XML, so It's impossible to work with it then. That is the code:

    DECLARE  @XMLwithOpenXML TABLE
    (
        Id              INT IDENTITY PRIMARY KEY,
        XMLData         XML,
        LoadedDateTime  DATETIME
    )

    INSERT INTO @XMLwithOpenXML(XMLData, LoadedDateTime)
    SELECT      CONVERT(XML, BulkColumn) AS BulkColumn
                ,GETDATE() 
    FROM OPENROWSET(BULK 'C:\temp\PP015.xml', SINGLE_CLOB) AS x;
    
    SELECT * FROM @XMLwithOpenXML

The second option is use the BCP to do the same, but I'm getting an error.

    DECLARE @sql NVARCHAR(500) 

    SET @sql = 'bcp [ExternaDB].[dbo].[xmltab] IN "C:\temp\PP015.xml" -T -c'
    EXEC xp_cmdshell  @sql

    select * from xmltab

I want to know if I'm on the correct way (How to work with an XML when is already in an SQL cell I know how to do it) and how I can BULK import the full XML into a cell without Length constraint.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

讽刺将军 2025-02-16 08:43:16

文件系统上XML文件的大小是多少？

请尝试以下解决方案。它与您的三个区别非常相似：

single_blob而不是single_clob
convert> convert（xml，bulkcolumn）
def default 代码>子句用于 LoadDateTime 列，

此外，您可以将SSIS用于任务。 SSIS具有没有XML文件大小限制的流XML源适配器。

SQL

DECLARE @tbl TABLE(
    ID INT IDENTITY PRIMARY KEY,
    XmlData XML,
    LoadedDateTime DATETIME DEFAULT (GETDATE())
);

INSERT INTO @tbl(XmlData)
SELECT BulkColumn
FROM OPENROWSET(BULK N'C:\temp\PP015.xml', SINGLE_BLOB) AS x;

SELECT * FROM @tbl;

What is the size of the XML file on the file system?

Please try the following solution. It is very similar to yours with three differences:

SINGLE_BLOB instead of SINGLE_CLOB
No need in CONVERT(XML, BulkColumn)
DEFAULT clause is used for the LoadedDateTime column

Additionally, you can use SSIS for the task. SSIS has a streaming XML Source Adapter with no XML file size limitation.

SQL

DECLARE @tbl TABLE(
    ID INT IDENTITY PRIMARY KEY,
    XmlData XML,
    LoadedDateTime DATETIME DEFAULT (GETDATE())
);

INSERT INTO @tbl(XmlData)
SELECT BulkColumn
FROM OPENROWSET(BULK N'C:\temp\PP015.xml', SINGLE_BLOB) AS x;

SELECT * FROM @tbl;

回复收藏 0 原文