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>

Answer 1

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;
        }
    }
}

Answer 2

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

1. 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.
2. 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.
3. 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.
4. 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.

1. What framework are you using (web api, mvc controller, asp.net, something else)
2. 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;
            }
        }
    }
}

Answer 3

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();
}

Answer 4

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.