Entity framework calling a FOR XML stored procedure truncates at 2033 characters

Question

I have a stored procedure which uses a FOR XML statement at the end of it, and returns me some XML.

I am using .NET 4 and the Entity Framework and when I do a function import of this stored procedure and try to call it through the Entity Framework it truncates the return at 2033 characters.

I swapped the Entity Framework for a traditional ADO.NET approach to call the stored procedure which had the same problem - truncated at 2033 characters - which is when I came across the following MSDN article explaining this is by-design and to use the "ExecuteXMLReader" method to overcome it:

http://support.microsoft.com/kb/310378

So this is working now as a temporary fix but I'd like to use Entity Framework function imports so I've not got ADO.NET code mixed up with EF code.

Is there some way I can use function imports in EF, return the XML and overcome the 2033 character limit?

Regards
bgs264

Ichthyornis

Answer 1

I ran into the same issue today.

The EF function call returns the XML in 2033-long string 'chunks' (e.g. if your XML was 5000 chars long you would receive 3 results: 2 of 2033 chars and 1 of 934 chars)

You can easily append these chunks to return a full list of the XML.

Chigger

Answer 2

I upvoted Fermin's answer. Response to Dementic (and anyone else), here is a code fragment.

From this:

using (var db = new MyEntities())
{
    IEnumerable<string> results = db.GetSomeXML(ProductCode);
    return results.FirstOrDefault();           
}

To this:

using System.Text;      //For the StringBuilder

using (var db = new MyEntities())
{
    StringBuilder retval = new StringBuilder();

    IEnumerable<string> results = db.GetSomeXML(ProductCode);
    foreach (var result in results)
        retval.Append(result);

    return retval.ToString();           
}

Hardwood

I've moved job (twice!) since I posted my original answer but from memory the above code is more or less what I had. – Chigger

@Hardwood can you please show the db.GetSomeXML() method ? Iam not able to implement this code with TSQL-XML datatype returning from stored procedure. There is no error, only the results variable is NULL. – Applejack

@Muflix, that GetSomeXML() is a generalised name for the (very) company specific Function Import of the EF stored procedure. I don't currently have access to that from here, but will have a look tonight / tomorrow. From memory, it would probably either have been nvarchar(max), or possibly TSQL-XML. We were using SQL 2008. – Hardwood