将大型XML文件插入XML列（在远程SQL Server上）的最佳方法

Question

假设我有这样一个表：

CREATE TABLE [dbo].[TBL_XML]
(
   [XmlFileID]       [BIGINT] IDENTITY (1, 1) NOT NULL,
   [FileName]        [NVARCHAR](500) NULL,   
   [XmlData]         [XML] NULL,
   [DateCreated]     [DATETIME] NOT NULL,
)

我目前使用的填充表格的方法如下：

using (SqlCommand cmd = new SqlCommand())
{
    cmd.CommandText = @"INSERT INTO [dbo].[TBL_XML] 
                                    ( [XmlData] , 
                                    [FileName] , 
                                    [DateCreated]
                                    ) 
                        VALUES (@XMLData, @FileName, GETDATE())";

    using (var xmlReader = new XmlTextReader(new FileStream(item.XmlFileName, FileMode.Open)))
    {
        cmd.Parameters.Add("@FileName", SqlDbType.NVarChar, 500).Value = System.IO.Path.GetFileName(item.XmlFileName);
        cmd.Parameters.Add(
        new SqlParameter("@XMLData", SqlDbType.Xml)
        {
            Value = new SqlXml(xmlReader)
        });

        SetConnectionParameters(cmd);

        cmd.ExecuteScalar());
    }
}

但是对于非常大的XML文件，这将无法工作，因为整个文件会加载到内存中，并且我会收到OutOfMemory异常。

在运行在不同计算机上的.net应用程序中，将一个大于100MB的XML文件插入到XMLData列中的最佳方法是什么？

由于SQL服务器将无法访问我的XML文件，因此批量插入是不可行的。

- BogdanB

---

我自己从未使用过，但是你考虑一下 SQLXML Bulk Load 实用程序 呢？它是一个 COM 对象，因此您仍然需要编写程序来使用它，但它不需要 SQL Server 直接访问 XML 文件。 - Pondlife

请注意，您不应使用new XmlTextReader()或new XmlTextWriter()。自.NET 2.0以来，它们已被弃用。请改用XmlReader.Create()或XmlWriter.Create()。 - John Saunders

我认为如果可能的话，你应该尝试将XML分块处理，并查看此链接是否对你有帮助： http://sortedbits.com/reading-big-xml-file-with-c-xmlreader/ - kailash gaur

@BogdanB: 你在使用哪个版本的SQL Server？ - Dominic Zukiewicz

Answer 1

以下是一种仅使用.NET进行分块的潜在方法。我没有尝试执行此操作，但它应该可以工作。

    public static ChunkedXmlInsert(XmlItem item)
    {
        int bufferSize = 65536;

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            CreateTempTable(connection);

            int position = 0;
            using (StreamReader textStream = File.OpenText(item.XmlFileName))
            {
                char[] buffer = new char[bufferSize];
                int length = textStream.Read(buffer, position, buffer.Length);
                long id = InsertFirstBlock(connection, new string(buffer, 0, length));

                while (textStream.EndOfStream == false)
                {
                    position += length;
                    length = textStream.Read(buffer, position, buffer.Length);
                    AppendBlock(connection, id, new string(buffer, 0, length));
                }
            }

            CopyRecordFromTemp(connection, id);
        }
    }

    private static void CreateTempTable(SqlConnection connection)
    {
        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandType = CommandType.Text;
            command.CommandText = @"CREATE TABLE #TBL_XML (
                                                              [XmlFileID] [BIGINT] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
                                                              [FileName] [NVARCHAR](500) NULL,
                                                              [XmlData] [NVARCHAR(MAX)] NULL,
                                                              [DateCreated] [DATETIME] NOT NULL
                                                          )";
            command.ExecuteNonQuery();
        }
    }

    private static long InsertFirstBlock(SqlConnection connection, string text)
    {
        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandType = CommandType.Text;
            command.CommandText = @"INSERT INTO #TBL_XML
                                                        ( [XmlData] , 
                                                          [FileName] , 
                                                          [DateCreated]
                                                        ) 
                                        VALUES (@XMLData, @FileName, GETDATE()); SELECT SCOPE_IDENTITY()";

            command.Parameters.AddWithValue("@FileName", System.IO.Path.GetFileName(item.XmlFileName));
            command.Parameters.AddWithValue("@XmlData", text);
            return (long)command.ExecuteScalar();
        }
    }

    private static void AppendBlock(SqlConnection connection, long id, string text)
    {
        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandType = CommandType.Text;
            command.CommandText = @"UPDATE #TBL_XML
                                            SET XmlData = XmlData + @xmlData
                                    WHERE XmlFileID = @XmlFileID";

            command.Parameters.AddWithValue("@XmlData", text);
            command.Parameters.AddWithValue("@XmlFileID", id);
            command.ExecuteNonQuery();
        }
    }

    private static long CopyRecordFromTemp(SqlConnection connection, long id)
    {
        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandType = CommandType.Text;
            command.CommandText = @"INSERT INTO [dbo].[TBL_XML] ([XmlData], [FileName], [DateCreated])
                                    SELECT CONVERT(xml, [XmlData]), [FileName], [DateCreated]
                                    FROM #TBL_XML
                                    WHERE XmlFileID = @XmlFileID; SELECT SCOPE_IDENTITY()";
            return (long)command.ExecuteScalar();
        }
    }

- JamieSee

Answer 2

看起来 SqlXml 参数类型接受 XmlReader 作为输入。

作为基于流的阅读器，这应该消除您正在经历的内存限制。

using (SqlCommand cmd = new SqlCommand())
{
    SqlParameter sqlParameter = cmd.CreateParameter();
    sqlParameter.Direction = System.Data.ParameterDirection.Input;
    sqlParameter.Value = new System.Data.SqlTypes.SqlXml(xmlReader);
    ...
}

- evgenyk

---

请注意，他已经按照您已经输入的内容进行操作。new SqlParameter("@XMLData", SqlDbType.Xml) { Value = new SqlXml(xmlReader) }); - CodeCowboyOrg

确实，你是正确的。我的假设是 'sqlParameter.Direction = System.Data.ParameterDirection.Input' 将流式传输 XML 数据到 SQL Server 中。但在查看内部实现后，令人惊讶的是，即使期望输入流，它也会将所有内容读入内存中。 - evgenyk

Answer 3

@xmlstring 是输入的 XML。

INSERT INTO @Temptbl           
   SELECT CONVERT(varchar(12), e.query('./Name/text()')) LogNumber             
   FROM @xmlstring.nodes('//RootNode/Root') AS n(e)     

select Temptbl     

- karthik

Answer 4

由于您需要将数据存储在远程SQL Server中，因此您需要通过网络传输整个文件内容。通常有两种方式：

• 将数据传输到远程服务器；
• 远程服务器从您的本地文件中读取数据。

第二种方式不在本问题的范围内。第一种方式可以通过几种方法来实现，假设您在远程服务器上实现了存储过程：

• 实现“上传”功能，将数据分部分传输，然后在服务器端连接它们并存储在数据库中；
• 在客户端对XML内容进行打包，然后在服务器端进行解包和存储；
• 以上方法的组合。

- Serg

Answer 5

我认为你应该创建一个SSIS包，将你的XML文件导入到SQL Server数据库中。 然后，如果你需要使用C#，你可以以编程方式执行这个包。 这样做更高效，并且它的工作效果非常好。

- K4timini

Answer 6

将XML文件存储在FileTable中。

- matte

Answer 7

将XML加载到数据库中的最佳方法是不要将XML加载到数据库中。如果您确实有这样的需求（而您可能没有），则应该考虑使用像Cassandra或Mongo这样的noSQL解决方案。

如果“需要”存储XML，那么很可能您在其他地方做出了错误的架构选择。请考虑是否有更好的表示信息的方式。 XML是一种交换格式，不适合长期保存信息。

话虽如此，您可以使用BCP或支持流式传输的OLEDB接口。以下是一个示例： 批量导入示例

- Thomas Kejser

---

或者RavenDB！使用@ThomasKejser的建议，XML变得非常容易搜索和索引。我以前在Oracle中存储过XML，最终你会编写两套数据访问层 - 一个用于数据库，另一个用于XML解析。不好玩:( - Dominic Zukiewicz

我倾向于同意@thomas的观点，即将大型XML文件存储在远程SQL服务器上是根本错误的。这需要重新架构。 - Maxime Rouiller

这完全没有回答问题。 - Jeremy Holovacs

只需更改整个存储格式，这样会更容易。LOL - Porco

@Porco 更改整个存储格式可能会更容易。如果您设计了一个需要使用XML进行存储的存储格式，那么您正在走向疯狂之路。关键在于，看起来OP试图解决错误的问题-而不是重新陈述问题，以便导致良好的设计。请注意，如果您坚持要存储XML，我在最后一段提供了信息（现在我还添加了一个小链接）。 - Thomas Kejser

Answer 8

您可以编写存储过程将大型 XML 文件插入到数据库中。在该存储过程中，您可以将 XML 文件输入作为 nvarchar(max) 发送。请注意，nvarchar(max) 可以接受多达 8000 个字符。

在您的代码中，主要流程是从文件流传递 xml，这是非常昂贵的操作。

如果您的文件超出了 nvarchar(max) 的大小，则可以在存储过程中使用更多的输入参数，然后将这些输入参数连接起来，以插入单个值作为 XML 文件。

- Rahul Neekhra

Answer 9

本文提供了一些有关使用LOB的背景信息。 请查看此处

我刚刚测试了带有xml字段的OPENROWSET BULK语法，它可以正常工作。如果您的SQL服务器可以访问该文件，则这是一个选项（我刚刚注意到这不是一个选项，因此我将进行更多实验）。否则，您需要尝试偏移写入解决方案。到目前为止，我无法使那个.WRITE方法与xml字段一起工作。

此文章指出您可以使用BCP插入XML。这需要安装SQL Server客户端工具。

- Ken

Answer 10

请使用 SQL XML Bulk Loader http://msdn.microsoft.com/en-us/library/ms171806.aspx。

- VenVig