Retrieving long XML data from SQL without XmlReader

Posted on 2 mins read

As I told in a previous post if you have to retrieve a great amount of XML generated by a FOR XML in a sql server 2005 environment, you cannot use the ExecuteScalar() method of the Command object. The executeScalar in fact returns only a small amount of XML, so the right way to do this is to use XmlReader.

Now I’m working in a project where we have the DAL written with Enterprise Library (I must admit that I do no like very much the approach of Entlib but this is another story). The concrete DAL is instantiated with IoC, but we have a generic implementation that using Entlib is capable of issuing the SQL query to different types of databases.

Now I have a stored that should return a lot of data in XML format, for Sql 2005 database we can use a FOR XML and all went good, for other database (like oracle) you can generate xml data with a stored procedure in PL-SQL. The decision not to create the XML in the Business Layer is only for simplicity. Now I face a problem, if I use the Generic Database Object of enterprise library, how can I retrieve the content returned with a Select.. FOR XML?

If I use ExecuteScalar the result will be truncated, If I use the XmlReader the concrete DAL cannot work with other types of database. But there is a different way to read data generated with a FOR XML. The reason is that SQL server return the XML in chunk of data, so you can use a simple IDataReader in this way.

1
2
3
4
5

StringBuilder aggregator = new StringBuilder();
using (IDataReader dr = CurrentDatabase.ExecuteReader(c)) {
    while (dr.Read())
        aggregator.Append((String) dr[0]);
}

You can simply use a StringBuilder, and reading with a IDataReader, for each row there will be only one column, of type string, that contain a fragment of the XML returned :D

alk.