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 can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in subqueries. The top-level FOR XML clause can be used only in the SELECT statement.
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
HttpControllerHandler
so it probably has something to do with where or how you are serving your response.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.
XmlException
so that would not be a possible failure point based on the Exception you are receiving.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.
WebApiConfig.cs
file. GlobalConfiguration.Configuration.Formatters.Add(new System.Net.Http.Formatting.XmlMediaTypeFormatter()); GlobalConfiguration.Configuration.Formatters.XmlFormatter.UseXmlSerializer = true;
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();
}
}
}
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;
}
}
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With