最有效的方法，从XML类型的列读取XML在ADO.net在SQL服务器？ [英] Most efficient way to read XML in ADO.net from XML type column in SQL server?

问题描述

使用SQL Server中的XML类型的列中，什么是最有效的方式回读这成的XmlDocument 的ADO.Net？

对于这个特殊的用途，一个的XmlDocument 是需要随机访问加载的文件。使用.NET 4.0（C＃）和SQL Server 2008 R2。

本来我们这是返回结果集的存储过程。当调用 SqlDataAdapter.Fill（数据表）获得的结果，只是返回的XML作为字符串。

然后我改变了这种有T-SQL返回类型XML的输出参数，并注册了这个在.net类型的输出参数 SqlDbType.Xml 。执行后，所产生的输出参数既有 .DbType 和 .SqlDbType 的DbType的。 XML - 但调用。价值返回一个类型 System.String 的

我的可以的通话 .SqlValue 此，它返回一个类型的 SqlTypes.SqlXml 。从这里，我可以调用它的。价值 - 刚刚返回字符串或 CreateReader - 它返回一个的XmlReader 。所以，我可以使用它来填充的XmlDocument 使用 XMLDocument.load方法（XmlReader的）。

主要的问题是 - 有什么好处这样做，或者我应该只是恢复到检索字符串 - 然后通过这个来新的XmlDocument（）的loadXML（字符串）？会使用 SQLXML 办法用XML要在SQL和.Net层之间更有效地传递 - 或将XML只是透明作为字符串反正对发射线？即使后来，我猜是， SqlXml.CreateReader 办法可能更有效，因为包含整个XML结构永远不会需要在创建一个字符串-Memory一次 - 并打算用这个现在

（或者，我一直在寻找使用 SqlDataReader的和 GetSqlXml 方法。有趣的是，目前只有对因此对于GetSqlXml，虽然<3其他结果href="http://stackoverflow.com/questions/5812733/how-to-get-xml-data-from-a-column-in-an-sql-table">How从在SQL表的列得到的XML数据？中似乎是最相关的结果，可以期待以解决不同的问题。）

这样做的倒数是相当简单：创建的XmlDocument ，添加一个类型的输入参数 SqlDbType.Xml ，传递的的XmlDocument 实例作为价值 - 和做

。

我意识到，我可以分析这些方法自己之间的差异 - 和计划这样做，一旦时间可用。只是找人谁可能已经有了答案，或者能提供一些替代方案，我还没有考虑或者说我不熟悉。

解决方案

无论你选择做应该使用一个XmlReader。不要强制SQL Server XML转换成文本形式的序列化。

---

更新：

SQL Server存储XML以高效的二进制格式。 SqlXml.CreateReader从二进制格式创建的XmlNode对象。否则，高效的XML已被序列化到一个字符串，在它上面必须再次进行解析。

With a XML type column in SQL server, what is the most efficient way to read this back into an XmlDocument in ADO.Net?

For this particular use, an XmlDocument is needed for random-access to the loaded document. Using .Net 4.0 (C#) and SQL Server 2008 R2.

Originally, we had a stored procedure that was returning a result set. When calling SqlDataAdapter.Fill(DataTable) to obtain the results, the XML is returned only as a string.

I then changed this to have the T-SQL return an output parameter of type 'XML', and registered this in .Net as an output parameter of type SqlDbType.Xml. After execution, the resulting output parameter has both a .DbType and .SqlDbType of DbType.Xml - but calling .Value returns a type of System.String.

I can call .SqlValue on this, which returns a type of SqlTypes.SqlXml. From here, I can call its .Value - which just returns a string, or CreateReader - which returns an XmlReader. So I could use this to populate the XmlDocument using XmlDocument.Load(XmlReader).

The main question is - is there any benefit to doing this, or should I just revert back to retrieving the string - then passing this to new XmlDocument().LoadXml(string)? Will using the SqlXml approach allow the XML to be passed between the SQL and the .Net layer more efficiently - or will the XML just be transparently transmitted as a string anyway "on the wire"? Even if the later, I'm guessing that the SqlXml.CreateReader approach may be more efficient, as a string containing the entire XML structure won't ever need to be created in-memory at once - and will plan on using this for now.

(Alternatively, I was looking at using a SqlDataReader and its GetSqlXml method. Interestingly, there are currently only 3 other results on SO for "GetSqlXml", and while How to get XML data from a column in an SQL table? seems to be the most relevant result, it is looking to address a different issue.)

The inverse of this was rather straight-forward: Create the XmlDocument, add an input parameter of type SqlDbType.Xml, pass-in the XmlDocument instance as the value - and done.

I realize that I can profile the differences between these methods myself - and plan to do so once the time is available. Just looking for anyone who may already have the answers, or can provide some alternatives that I haven't yet considered or that I'm not familiar with.

解决方案

Whatever choice you make should use an XmlReader. Don't force SQL Server to serialize the XML into text form.

---

Update:

SQL Server stores XML in an efficient binary format. SqlXml.CreateReader creates XmlNode objects from that binary format. Otherwise, the efficient XML has to be serialized to a string, and on top of it has to be parsed again.

这篇关于最有效的方法，从XML类型的列读取XML在ADO.net在SQL服务器？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！