Most efficient way to read XML in ADO.net from XML type column in SQL server?

3
votes

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.

1
sql-serverxmlc#-4.0ado.net
Whatever choice you make should use an XmlReader. Don't force SQL Server to serialize the XML into text form. – John Saunders
@JohnSaunders - that is my question, which you just helped clarify. Does using an XmlReader prevent SQL server from having to serialize the XML (and .Net from having to deserialize it)? Or are the APIs just exposing different functionality, while otherwise working identically behind the API? – ziesemer
Yes, it does. 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. – John Saunders
@JohnSaunders - please convert your comments to an answer to that I can accept it. Any sources you could include would also be very appreciated for future reference. Thanks! – ziesemer

1 Answers

4
votes

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.