I get xml (average size is about 2 GB) and xsd, I need to add some of the information from them to the ms sql database. Are there any design patterns or accepted norms in this case? What to use - an intermediate embedded database or there are more beautiful solutions?

update This XML is essentially a table, the root tag is there, and there are identical tags in it, in which there are many identical attributes with different values, roughly speaking a two-dimensional table. From this XML I need only a part of the data that I need to add to the working database.

  • Concretize the question. In XML, data refer to a single table or to many? Is Xml a display of a document with multiple structures or an impression of a single table? - mals
  • @MaLS updated the question - e1s

3 answers 3

To load a large amount of data into MSSQL, you need to use SqlBulkCopy .

The data from the file will need to be read and filtered in streaming mode while you will need to implement the IDataReader interface. An example is here IDataReader implementation + SqlBulkCopy .

Streaming reading from XML can be done using the XmlReader

  • Stream reading via XmlReader is understandable, but how to connect them later with IDataReader is not - e1s
  • unless example IDataReader implementation + SqlBulkCopy does not explain? Then this is another big question. - mals
  • updated the question of how to associate it with IDataReader I do not understand - e1s
  • You distorted the essence of the current issue. Please delete the changes and set a new "How to link IDaraReader with XmlReder for SqlBulkCopy?" because this is another topic. - mals
  • Good, ATP for correction - e1s

Size xml at least 1 terabyte. Use XDocument. With it, I processed files of several gigabytes, while the speed of data sampling was quite comfortable. You can also upload to the database using ADO.NET. It uses deferred commit to db.

  • And how do you load large files in XDocument? - e1s

If only part of the data is needed, then use LINQ to XML. There you can set the conditions of selection.

  1. Here is an article on this topic.

  2. But how to work with large files

  3. The XStreamingElement class is described here.