零碎的XML大容量加载到C＃中的SQL Server [英] Fragmented XML Bulk Load to SQL Server in C#

问题描述

我有一个XML，其中包含来自不同域的扫描系统的信息。 XML对应于嵌套在数据库中的表，如下所示：

I have an XML that contain information resulted from scanning systems on different domains. The XML corresponds to the tables in database that are nested as follows:

Domains
   Computers
      Volumes
          Folders
              Files

我的目标是将XML加载到相应的表中。
由于单个XML文件太大而无法加载到数据库中，因此我必须将其分块为较小的文件。
如何格式化XML，以便上传者知道一个文件是最后一个文件的继续，并且它不会为已添加的父节点生成其他密钥。
有人有这样做的经验吗？这是最快的上传方式吗？
非常感谢！
这是我用来上传xml的代码：

My goal is to load the XML into the corresponding tables. Since one single XML file would be so large to load into database, I have to chunk it into smaller one. How can I format the XMLs so the uploader knows one file is a continue of the last file and it does not generate additional keys for a parent node that is already added. Does any body any experience doing this? Is this the fastest way to upload? Many Thanks! Here is the code that I use to upload the xml:

        static void BulkLoadXML()
    {
        try
        {
            string sPath = @"C:\XMLFiles\";


            string FileName, xsdPath, xmlPath;
            FileName = "TestAuto";

            xmlPath = sPath + FileName + ".xml";
            xsdPath = sPath + FileName + ".xsd";

            SQLXMLBULKLOADLib.SQLXMLBulkLoad4 objBL = new SQLXMLBULKLOADLib.SQLXMLBulkLoad4();

            objBL.ConnectionString = @"Provider=sqloledb; server=srv1;database=MyTest;User ID=sa;Password=psw;Connection Timeout=60";

            objBL.ErrorLogFile = "error.xml";
            objBL.KeepIdentity = false;

            objBL.Execute(xsdPath, xmlPath);
        }
        catch (Exception e)
        {
            Console.WriteLine(e.ToString());
            Console.Read();
        }
        Console.Read();
    }

推荐答案

我做了很多研究这是部分解决问题的想法：

I did a a lot of research and here is an idea that partially solves the problem:

http://rakeshbajania.wordpress.com/2011/01/01/prevent-duplicate-entry-when-using-sqlbulkcopy/

因此，想法是为表定义唯一索引，并将IGNORE_DUP_KEY设置为ON。
唯一的问题是数据库返回错误并且不上载子节点。
最后，我决定创建由xml块逐个加载的临时表，并在使用存储过程将它们的记录发送到主表后清空它们。

So the idea is to define a unique index for tables and set the IGNORE_DUP_KEY to ON. The only problem is that the DB returns the error and does not upload the children nodes. In the end, I decided to create temp tables that are loaded by xml chunks one by one and empty them after sending their records to the main tables using stored procedures.

这篇关于零碎的XML大容量加载到C＃中的SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！