Best Way To Pass A Huge Xml Parameter To Stored Procedure

Actually I m trying to pass a huge Xml to my stored procedure, but I always get an out of memory exception, cause I m trying to pass a string which has a limit size to 2G. So is th

Solution 1:

Don't use XmlDocument - it will take about 10x the amount of memory as your source document to load the DOM fully into memory.

Use XmlReader and XmlWriter, or XDocument if you need to do manipulation of the document before passing it to SQL. These will work because they will do streaming processing (particularly XmlReader/XmlWriter, which are heavily optimized for forward-only reading of XML data), instead of trying to load the entire document and its DOM all at once (like XmlDocument).

Load well formed XML from a file:

command.Parameters.Add(new SqlParameter("@xmlParameterName", SqlDbType.Xml)
{
    Value = new SqlXml(XmlReader.Create("C:\\path\\to\\file.xml"));
});

Load from XML Data in a stream

Stream s;
// XML is in this stream
command.Parameters.Add(new SqlParameter("@xmlParameterName", SqlDbType.Xml)
{
    Value = new SqlXml(XmlReader.Create(s));
});

Load from an XDocument:

XDocument xd = XDocument.Load/.Parse/etc....
command.Parameters.Add(new SqlParameter("@xmlParameterName", SqlDbType.Xml)
{
    Value = new SqlXml(xd.Root.CreateReader());
});

Solution 2:

have you tried using StringBuilder reference