Getting XML from stored procedure

Question

I am getting an output of XML from a stored procedure. What I am trying to do is get that XML and pass it out via ASP.NET:

public XmlDocument GetPunchListXml(string communityDesc)
{
    try
    {
        using (connection = new SqlConnection(connectionString))
        {
             connection.Open();

             using (SqlCommand command = new SqlCommand("GetPunchList", connection))
             {
                   command.CommandType = CommandType.StoredProcedure;

                   SqlParameter parameter1 = new SqlParameter("@communityDesc", SqlDbType.VarChar);
                   parameter1.Value = communityDesc;
                   parameter1.Direction = ParameterDirection.Input;
                   command.Parameters.Add(parameter1);

                   var doc = new XmlDocument();
                   var reader = command.ExecuteXmlReader();
                   if (reader.Read())
                   {
                       doc.Load(reader);
                   }

                   return doc;
               }
           }
      }
      finally
      {
         connection.Close();
      }
}

but I keep getting these errors:

<Error>
    <Message>An error has occurred.</Message>
    <ExceptionMessage>
        The 'ObjectContent`1' type failed to serialize the response body for content type 'application/xml; charset=utf-8'.
    </ExceptionMessage>
    <ExceptionType>System.InvalidOperationException</ExceptionType>
    <StackTrace/>
    <InnerException>
        <Message>An error has occurred.</Message>
        <ExceptionMessage>
            Type 'System.Xml.XmlDocument' is an invalid collection type since it does not have a valid Add method with parameter of type 'System.Object'.
        </ExceptionMessage>
        <ExceptionType>
            System.Runtime.Serialization.InvalidDataContractException
        </ExceptionType>
        <StackTrace>
            at System.Runtime.Serialization.DataContract.DataContractCriticalHelper.ThrowInvalidDataContractException(String message, Type type)
            at WriteArrayOfanyTypeToXml(XmlWriterDelegator , Object , XmlObjectSerializerWriteContext , CollectionDataContract )
            at System.Runtime.Serialization.CollectionDataContract.WriteXmlValue(XmlWriterDelegator xmlWriter, Object obj, XmlObjectSerializerWriteContext context)
            at System.Runtime.Serialization.XmlObjectSerializerWriteContext.WriteDataContractValue(DataContract dataContract, XmlWriterDelegator xmlWriter, Object obj, RuntimeTypeHandle declaredTypeHandle)
            at System.Runtime.Serialization.XmlObjectSerializerWriteContext.SerializeWithoutXsiType(DataContract dataContract, XmlWriterDelegator xmlWriter, Object obj, RuntimeTypeHandle declaredTypeHandle)
            at System.Runtime.Serialization.DataContractSerializer.InternalWriteObjectContent(XmlWriterDelegator writer, Object graph, DataContractResolver dataContractResolver)
            at System.Runtime.Serialization.DataContractSerializer.InternalWriteObject(XmlWriterDelegator writer, Object graph, DataContractResolver dataContractResolver)
            at System.Runtime.Serialization.XmlObjectSerializer.WriteObjectHandleExceptions(XmlWriterDelegator writer, Object graph, DataContractResolver dataContractResolver)
            at System.Runtime.Serialization.DataContractSerializer.WriteObject(XmlWriter writer, Object graph)
            at System.Net.Http.Formatting.XmlMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, HttpContent content)
            at System.Net.Http.Formatting.XmlMediaTypeFormatter.WriteToStreamAsync(Type type, Object value, Stream writeStream, HttpContent content, TransportContext transportContext, CancellationToken cancellationToken)
                --- End of stack trace from previous location where exception was thrown ---
            at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
            at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
            at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
            at System.Web.Http.WebHost.HttpControllerHandler.<WriteBufferedResponseContentAsync>d__1b.MoveNext()
        </StackTrace>
    </InnerException>
</Error>

What am I doing wrong? Is there something wrong with the way I am trying to get the XML or would be an issue with the XML itself?

I have tried the following:

var doc = new XmlDocument();
string s = "";
using (XmlReader reader = command.ExecuteXmlReader())
{
    while (reader.Read())
    {
        //doc.Load(reader);
        s = reader.ReadOuterXml();
        doc.LoadXml(s);
    }
}
return doc;

and got these errors:

<Error>
<Message>An error has occurred.</Message>
<ExceptionMessage>
The 'ObjectContent`1' type failed to serialize the response body for content type 'application/xml; charset=utf-8'.
</ExceptionMessage>
<ExceptionType>System.InvalidOperationException</ExceptionType>
<StackTrace/>
<InnerException>
<Message>An error has occurred.</Message>
<ExceptionMessage>
Type 'System.Xml.XmlDocument' is an invalid collection type since it does not have a valid Add method with parameter of type 'System.Object'.
</ExceptionMessage>
<ExceptionType>
System.Runtime.Serialization.InvalidDataContractException
</ExceptionType>
<StackTrace>
at System.Runtime.Serialization.DataContract.DataContractCriticalHelper.ThrowInvalidDataContractException(String message, Type type) at WriteArrayOfanyTypeToXml(XmlWriterDelegator , Object , XmlObjectSerializerWriteContext , CollectionDataContract ) at System.Runtime.Serialization.CollectionDataContract.WriteXmlValue(XmlWriterDelegator xmlWriter, Object obj, XmlObjectSerializerWriteContext context) at System.Runtime.Serialization.XmlObjectSerializerWriteContext.WriteDataContractValue(DataContract dataContract, XmlWriterDelegator xmlWriter, Object obj, RuntimeTypeHandle declaredTypeHandle) at System.Runtime.Serialization.XmlObjectSerializerWriteContext.SerializeWithoutXsiType(DataContract dataContract, XmlWriterDelegator xmlWriter, Object obj, RuntimeTypeHandle declaredTypeHandle) at System.Runtime.Serialization.DataContractSerializer.InternalWriteObjectContent(XmlWriterDelegator writer, Object graph, DataContractResolver dataContractResolver) at System.Runtime.Serialization.DataContractSerializer.InternalWriteObject(XmlWriterDelegator writer, Object graph, DataContractResolver dataContractResolver) at System.Runtime.Serialization.XmlObjectSerializer.WriteObjectHandleExceptions(XmlWriterDelegator writer, Object graph, DataContractResolver dataContractResolver) at System.Runtime.Serialization.DataContractSerializer.WriteObject(XmlWriter writer, Object graph) at System.Net.Http.Formatting.XmlMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, HttpContent content) at System.Net.Http.Formatting.XmlMediaTypeFormatter.WriteToStreamAsync(Type type, Object value, Stream writeStream, HttpContent content, TransportContext transportContext, CancellationToken cancellationToken) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter.GetResult() at System.Web.Http.WebHost.HttpControllerHandler.<WriteBufferedResponseContentAsync>d__1b.MoveNext()
</StackTrace>
</InnerException>
</Error>

You should be able to do it the way you're doing it, but for debugging purposes, I'd try getting the XML out of the XmlReader first as a string, so that you can output what it returns, then you can use the LoadXml() method on your XmlDocument to try to load in the XML as a string. See if it works that way first, then figure out what's going wrong with your Load() method approach. Also, you should wrap your reader in a using statement: `using (var reader = command.ExecuteXmlReader()) { // Do stuff here }` — ManoDestra, Apr 13 '16 at 16:00
This may also be of use: http://stackoverflow.com/questions/5423980/how-do-i-return-xml-from-a-stored-procedure — ManoDestra, Apr 13 '16 at 16:10
@ManoDestra I tried the suggested returning a string, it works, but I really need it to be in XML Format. — user979331, Apr 13 '16 at 16:20
The string should be XML already. Did you try loading up the string into your XmlDocument via LoadXml? If you did, did it error and what error did you get? — ManoDestra, Apr 13 '16 at 16:21
@ManoDestra I have updated my question with what I tried and the errors I get. — user979331, Apr 13 '16 at 16:28
This error looks like an asp.net error not a SQL or ado.net error. How are you using the resulting xmldocument? — Martin Smith, Apr 13 '16 at 17:12
Yeah, that looks to me like you're trying to add an XmlDocument to the response, then trying to iterate over it like a list or something in your view. — ManoDestra, Apr 13 '16 at 17:22
It will all depend on what you can serialize via ASP.Net. Try injecting your XmlDocument into the response, if you can, and then just call a simple property on the XmlDocument, like document.OuterXml and put that string into a textarea, something like that. If you can do that simple thing, then serialization is fine and it's some other part of your code that's wrong. To me, it looks like your code is expecting a list somewhere and you're giving it an XmlDocument to work with. Without seeing the code that's doing the injection and your view code, I can't say much more, I'm afraid. — ManoDestra, Apr 13 '16 at 18:18
`Type 'System.Xml.XmlDocument' is an invalid collection type since it does not have a valid Add method with parameter of type 'System.Object'.` What this tells me, is that there's a type conversion that's failing. In particular with your `doc.Load(reader);` code. I think (correct me if i'm wrong) you need to specify the index of the value you're looking for in the reader, as per how you do with standard data readers? If so, you'd want something like this: `doc.Load(reader(0));` — user2366842, Apr 22 '16 at 20:11
@user979331 - could specify exactly where (on what line) the exception is thrown? Based on your stack traces it appears as if the failure occurs outside of this method, possibly after it in which case you might be troubleshooting the wrong code. — Igor, Apr 23 '16 at 09:33

score 7 · Accepted Answer · edited May 23 '17 at 12:10

The issue is probably where you are using the result of your XmlDocument as I also can't find any issue with the code. As to why I believe this

None of your stack traces show any calls to your posted code. They actually point to an exception started at HttpControllerHandler so it probably has something to do with where or how you are serving your response.
Based on the documentation the call SqlCommand.ExecuteXmlReader can throw the InvalidOperationException but it reads that this would only occur if The SqlConnection closed or dropped during a streaming operation. For more information about streaming, see SqlClient Streaming Support.
Based on the documentation the call XmlDocument.Load only throws exception XmlException so that would not be a possible failure point based on the Exception you are receiving.
After Googling the exception Message I found previous users had experienced this error due to Web API configuration problems, so again nothing to do with loading in Xml from the database.

Speculation on the Solution

This is speculation as I do not know what framework you are using or if this is the WebApi method or a method that serves up the result further down the call stack.

So the fix for you is to get your data into a format that can be serialized by whatever framework you are using. For more help you need to provide.

What framework are you using (web api, mvc controller, asp.net, something else)
If web Api - The method end point being called that is providing the data. If this is that method then the error is the fact that you are trying to return an XmlDocument directly.
- Make sure the correct formatters are configured in your WebApiConfig.cs file. GlobalConfiguration.Configuration.Formatters.Add(new System.Net.Http.Formatting.XmlMediaTypeFormatter()); GlobalConfiguration.Configuration.Formatters.XmlFormatter.UseXmlSerializer = true;
- See this previous SO answer on how you could change your method to work.
- I would recommend keeping this one as is and marking it as private and then adding the Api method which calls this method and formats the results to something that could be passed back to the client.

Web Api Fix

I can duplicate the exact error and message by returning a XmlDocument as the response in a Web API controller. This then illustrates again that it has nothing to do with the content of the method but more where is the XmlDocument used in the response. If you are returning a XmlDocument directly in the response (so not a part of a larger object) you can I can fix it by adding a new HttpContent type that knows how to deserialize (extract the actual Xml content) the XmlDocument. This is based on this previous SO answer posted by Darrel Miller. Here is that fix in a complete self contained example.

using System.IO;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Threading.Tasks;
using System.Web.Http;
using System.Xml;

namespace WebApiTests.Controllers
{
    public class HomeController : ApiController
    {
        const string sampleData = "<?xml version=\"1.0\"?><catalog><book id=\"bk101\"><author>Gambardella, Matthew</author><title>XML Developer's Guide</title><genre>Computer</genre><price>44.95</price><publish_date>2000-10-01</publish_date><description>An in-depth look at creating applications with XML.</description></book></catalog>";
        public HttpResponseMessage Get()
        {
            var doc = new XmlDocument();
            doc.LoadXml(sampleData);

            return new HttpResponseMessage()
            {
                RequestMessage = Request,
                Content = new XmlContent(doc)
            };
        }
    }

    public class XmlContent : HttpContent
    {
        private readonly MemoryStream _Stream = new MemoryStream();

        public XmlContent(XmlDocument document)
        {
            document.Save(_Stream);
            _Stream.Position = 0;
            Headers.ContentType = new MediaTypeHeaderValue("application/xml");
        }

        protected override Task SerializeToStreamAsync(Stream stream, System.Net.TransportContext context)
        {
            _Stream.CopyTo(stream);
            var tcs = new TaskCompletionSource<object>();
            tcs.SetResult(null);
            return tcs.Task;
        }

        protected override bool TryComputeLength(out long length)
        {
            length = _Stream.Length;
            return true;
        }

        protected override void Dispose(bool disposing)
        {
            if(_Stream != null)
                _Stream.Dispose();
        }
    }
}

General code fixes

Your method does have some problems in it as it relates to the SqlConnection. Below is the cleaned up code with explanations.

public XmlDocument GetPunchListXml(string communityDesc)
{
    // 1. Use a new SqlConnection everywhere and do not register SqlConnection as a field on the class.
    // This is a Microsoft recommended best practice. Sql Server handles connection pooling so the call new SqlConnection is very cheap.
    // 2. a using block will close and dispose the connection for you
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        using (SqlCommand command = new SqlCommand("GetPunchList", connection))
        {
            command.CommandType = CommandType.StoredProcedure;

            SqlParameter parameter1 = new SqlParameter("@communityDesc", SqlDbType.VarChar);
            parameter1.Value = communityDesc;
            parameter1.Direction = ParameterDirection.Input;
            command.Parameters.Add(parameter1);

            // wrap your DataReader in a using block
            using (var reader = command.ExecuteXmlReader())
            {
                var doc = new XmlDocument();
                if (reader.Read())
                {
                   doc.Load(reader);
                }
                return doc;
            }
        }
    }
}

score 0 · Answer 2 · answered Apr 23 '16 at 09:08

Change your below code,

var doc = new XmlDocument();
var reader = command.ExecuteXmlReader();
if (reader.Read())
{
   doc.Load(reader);
}

As below,

XmlDocument doc = new XmlDocument();
XmlReader reader = command.ExecuteXmlReader();
if (reader.Read())
{
    doc.Load(reader);
}

If still not working, please share your xml which you return from SP.

score 0 · Answer 3 · answered Apr 23 '16 at 09:13

XmlDocument is not serializable, return a string representation of the XML instead

using (var stringWriter = new StringWriter())
using (var xmlTextWriter = XmlWriter.Create(stringWriter))
{
    xmlDoc.WriteTo(xmlTextWriter);
    xmlTextWriter.Flush();
    return stringWriter.GetStringBuilder().ToString();
}

score 0 · Answer 4 · answered Mar 21 '17 at 18:51

You can create your own simple MediaTypeFormatter in ASP.NET Web API to stream an XmlDocument directly to a response. A sample implementation is at the end of this answer.

Register it in the public static void Register(HttpConfiguration config) method of the autogenerated WebApiConfig class that you get when creating a new project with the VS wizard.

// This custom media type converter must be inserted before the default media type converter that handles application/xml, do not use config.Formatters.Add(...
config.Formatters.Insert(0, new MediaTypeFormatters.XmlDocumentMediaTypeFormatter());

In a controller class method do something similar to this

private HttpResponseMessage Get()
{
    XmlDocument doc = new XmlDocument();
    XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", "utf-8", null);
    doc.AppendChild(dec);
    XmlElement rootElement = doc.CreateElement("user");
    doc.AppendChild(rootElement);
    rootElement.SetAttribute("id", "1");

    var response = Request.CreateResponse(HttpStatusCode.OK, doc, "application/xml");
    return (response);
}

Sample custom MediaTypeFormatter to stream an XmlDocument to a response.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Net.Http.Formatting;
using System.Net.Http.Headers;
using System.IO;
using System.Net.Http;
using System.Threading.Tasks;
using System.Net;
using System.Xml;

namespace Take5.MediaTypeFormatters
{
    public class XmlDocumentMediaTypeFormatter : MediaTypeFormatter
    {
        private static Type _supportedType = typeof(XmlDocument);

        public XmlDocumentMediaTypeFormatter()
        {
            SupportedMediaTypes.Add(new MediaTypeHeaderValue("application/xml"));
        }

        public override bool CanReadType(Type type)
        {
            return type == _supportedType;
        }

        public override bool CanWriteType(Type type)
        {
            return type == _supportedType;
        }

        public override Task<object> ReadFromStreamAsync(Type type, Stream readStream, HttpContent content, IFormatterLogger formatterLogger)
        {
            var taskSource = new TaskCompletionSource<object>();
            try
            {
                System.Diagnostics.Debug.Assert(false, "Not fully implemented, get doc to read from readStream.");
                var doc = new XmlDocument();
                taskSource.SetResult(doc);
            }
            catch (Exception e)
            {
                taskSource.SetException(e);
            }
            return taskSource.Task;
        }

        public override Task WriteToStreamAsync(Type type, object value, Stream writeStream, HttpContent content, TransportContext transportContext)
        {
            var taskSource = new TaskCompletionSource<object>();
            try
            {
                XmlDocument doc = (XmlDocument)value;
                XmlWriter writer = XmlWriter.Create(writeStream, new XmlWriterSettings() { Indent = true });
                doc.WriteContentTo(writer);
                writer.Flush();

                taskSource.SetResult(null);
            }
            catch (Exception e)
            {
                taskSource.SetException(e);
            }
            return taskSource.Task;
        }
    }
}

Getting XML from stored procedure

4 Answers4

Speculation on the Solution

Web Api Fix

General code fixes