Saving a huge dataset into SQL table in a XML column - MS SQL, C#.Net

Question

Hello all

I'm having a requirement where i've to save a dataset which has multiple tables in it in a sql table XML column by converting into an XML.

The problem is that in some cases the dataset grows extremely huge and i get OutOfMemoryException.

What i basically do is that i convert the dataset into an xml file and save it in the local disk and then load the XML again and send it as a stored procedure parameter. But when i write the dataset xml into the disk the file sizes over 700 Mb and when i load it in the XMLDocument object in memory i get OutOfMemoryException.

How can get the dataset xml without saving it in a file and re-reading it again?

Code For Reading the XML from the dataset

MemoryStream msXML = new MemoryStream();
MemoryStream msXMLSchema = new MemoryStream();

XmlWriter xmlWriter = XmlWriter.Create(msXML);
XmlWriter xmlSchemaWriter = XmlWriter.Create(msXMLSchema);

dsContract.WriteXml(xmlWriter);
dsContract.WriteXmlSchema(xmlSchemaWriter);

xmlWriter.WriteStartDocument();
xmlWriter.WriteEndDocument();

xmlSchemaWriter.WriteStartDocument();
xmlSchemaWriter.WriteEndDocument();

XmlReader xmlReader = XmlReader.Create(msXML);
XmlReader xmlSchemaReader = XmlReader.Create(msXMLSchema);

XmlDocument contractHistoryXMLDoc = new XmlDocument();
XmlDocument contractHistoryXMLSchemaDoc = new XmlDocument();

contractHistoryXMLDoc.Load(xmlReader);
contractHistoryXMLSchemaDoc.Load(xmlReader);

Code for Saving the XML in the XML Column

Sending them as USP parameters

param = new SqlParameter("@XmlValue", SqlDbType.Xml);
param.Direction = ParameterDirection.Input;
param.Value = new XmlNodeReader(historyXML);
sqlcom.Parameters.Add(param);
param = new SqlParameter("@xmlSchemaHistory", SqlDbType.Xml);
param.Direction = ParameterDirection.Input;
param.Value = new XmlNodeReader(historyXMLSchema);
sqlcom.Parameters.Add(param);

Thank You

NLV

Answer 1

Use the Data.DataSet.GetXML() Method. It will return a huge string.

Answer 2

We divided the DataSet and saved it in multiple XML columns.