将巨大的 xml 参数传递给存储过程的最佳方法 [英] Best way to pass a huge xml parameter to stored procedure
问题描述
实际上我试图将一个巨大的Xml
传递给我的存储过程,但我总是得到一个内存异常
,因为我试图传递一个string
其大小限制为 2G.那么有没有办法做到这样的事情.
不要使用 XmlDocument
- 将 DOM 完全加载到内存中需要大约 10 倍于源文档的内存量.
如果您需要在将文档传递给 SQL 之前对其进行操作,请使用 XmlReader
和 XmlWriter
或 XDocument
.它们会起作用,因为它们将进行流式处理(特别是 XmlReader/XmlWriter,它们针对 XML 数据的前向读取进行了大量优化),而不是尝试一次加载整个文档及其 DOM(如 XmlDocument).>
从文件加载格式良好的 XML:
command.Parameters.Add(new SqlParameter("@xmlParameterName", SqlDbType.Xml){Value = new SqlXml(XmlReader.Create("C:\\path\\to\\file.xml"));});
从流中的 XML 数据加载
Stream s;//XML 在这个流中command.Parameters.Add(new SqlParameter("@xmlParameterName", SqlDbType.Xml){Value = new SqlXml(XmlReader.Create(s));});
从 XDocument 加载:
XDocument xd = XDocument.Load/.Parse/etc....command.Parameters.Add(new SqlParameter("@xmlParameterName", SqlDbType.Xml){值 = 新 SqlXml(xd.Root.CreateReader());});
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 there how to to such a thing.
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());
});
这篇关于将巨大的 xml 参数传递给存储过程的最佳方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!