Large Datatable with sqlXML Column throwing System.OutOfMemoryException

Question

I have a datatable which has the following structure

DataColumn sourceTblCol = new DataColumn("SourceTableName", typeof(System.Data.SqlTypes.SqlString));
                    importXMLMainDt.Columns.Add(sourceTblCol);
                    DataColumn ordinalPosCol = new DataColumn("OrdinalPosition", typeof(System.Data.SqlTypes.SqlInt32));
                    importXMLMainDt.Columns.Add(ordinalPosCol);
                    DataColumn grpNameCol = new DataColumn("GroupName", typeof(System.Data.SqlTypes.SqlString));
                    importXMLMainDt.Columns.Add(grpNameCol);
                    DataColumn entityTypeIDCol = new DataColumn("EntityTypeID", typeof(System.Data.SqlTypes.SqlInt32));
                    importXMLMainDt.Columns.Add(entityTypeIDCol);
                    DataColumn entityNameCol = new DataColumn("EntityName", typeof(System.Data.SqlTypes.SqlString));
                    importXMLMainDt.Columns.Add(entityNameCol);
                    DataColumn exportXMLCol = new DataColumn("ExportData", typeof(System.Data.SqlTypes.SqlXml));
                    importXMLMainDt.Columns.Add(exportXMLCol);

I am looping through all the xml files in a directory and adding that XML content as sqlXML in the datatable

xmlFiles = Directory.GetFiles(this.WrkImportFilePath, "*.xml");
                    foreach (string filename in xmlFiles)
                    {

                                using (XmlReader xmlReaderObj = XmlReader.Create(filename))
                                {
                                    while (xmlReaderObj.Read())
                                    {
                                        sqlxmlObj = new SqlXml(xmlReaderObj);
                                    }
                                    xmlReaderObj.Close();
                                }
                                importXMLMainDt.Rows.Add(srcTableName, ordinalPos, grpName, entityTypeID, string.Empty, sqlxmlObj);
                                sqlxmlObj = null;

                        }
                    }

It is working fine for 2500 XML files but throwing exception if the number of file goes more that 3000.The max number of files can be upto 12,000. I need to create this datatable and pass it as table valued parameter in stored procedure.So how do I handle this problem?

Why isn't inserting them individually an option, that is, just call the stored procedure more than once? Even if you do get this to work with all documents, SQL Server would be forced to allocate a huge amount of memory as well, not to mention a big chunk of transaction log. Is it a business requirement that all the inserts happen transactionally? — Jeroen Mostert
– Jeroen Mostert, Commented May 11, 2016 at 13:37

Gottfried Lesigang · Accepted Answer · 2016-05-11 13:42:44Z

2

You get an out-of-memory-exception because you try to load all your files in one go and then send it to the database in one go. The machine's memory is just not big enough...

You have several options:

Buy huge amounts of RAM :-)
Transfer the data in parts (one-by-one ?)
If the XML files reside in a location you can reach from SQL Server directly, you might - much easier! - push the file names only and load the XML into a DB-table's column via TSQL.

Just in case you want to try option 3:

Try to load one of your files. If this works, it should work with all of them...

DECLARE @yourXML AS XML=
(
SELECT CONVERT(XML, BulkColumn,2) AS BulkColumn
FROM OPENROWSET(BULK 'PathToFile.xml', SINGLE_BLOB) AS x
);
SELECT @yourXML;

answered May 11, 2016 at 13:42

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pallav Over a year ago

Option 2 solved my problem thanks option 3 might not work due to filepath permission as DB server and application server are different.Also in 64 bit OS this problem is not happening.

Collectives™ on Stack Overflow

Large Datatable with sqlXML Column throwing System.OutOfMemoryException

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related