我正在从存储过程中获取 XML 输出。我想要做的是获取该 XML 并通过 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();
      }
}

但我不断收到这些错误:

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

我做错了什么？我尝试获取 XML 的方式有问题还是 XML 本身有问题？

我尝试了以下方法:

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;

并得到这些错误:

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

最佳答案

问题可能出在您使用 XmlDocument 结果的地方，因为我也找不到代码的任何问题。至于我为什么这么认为

1. 您的堆栈跟踪都没有显示对您发布的代码的任何调用。他们实际上指出了一个始于 HttpControllerHandler 的异常，因此它可能与您在何处或如何提供响应有关。
2. 根据文档调用 SqlCommand.ExecuteXmlReader可以抛出 InvalidOperationException，但它表明这只会在 The SqlConnection 在流式操作期间关闭或删除时发生。有关流式处理的更多信息，请参阅 SqlClient 流式处理支持。
3. 根据文档调用 XmlDocument.Load仅抛出异常 XmlException，因此根据您收到的异常，这不会成为可能的故障点。
4. 在谷歌搜索异常消息后，我找到了 previous users had experienced this error由于 Web API 配置问题，因此再次与从数据库加载 Xml 无关。

对解决方案的推测

这是推测，因为我不知道您使用的是什么框架，也不知道这是 WebApi 方法还是在调用堆栈下方提供结果的方法。

因此，您的解决方法是将您的数据转换为可以由您使用的任何框架序列化的格式。如需更多帮助，您需要提供。

1. 您使用的是什么框架(网络 API、mvc Controller 、asp.net 等)
2. 如果是 web Api - 正在调用的提供数据的方法端点。如果这是该方法，则错误是您试图直接返回 XmlDocument。
   • 确保在您的 WebApiConfig.cs 文件中配置了正确的格式化程序。 GlobalConfiguration.Configuration.Formatters.Add(new System.Net.Http.Formatting.XmlMediaTypeFormatter()); GlobalConfiguration.Configuration.Formatters.XmlFormatter.UseXmlSerializer = true;
   • 参见 this previous SO answer关于如何改变你的工作方式。
   • 我建议保持原样并将其标记为私有(private)，然后添加调用此方法并将结果格式化为可以传回客户端的内容的 Api 方法。

网络 API 修复

我可以通过返回一个 XmlDocument 作为 Web API Controller 中的响应来复制确切的错误和消息。这再次说明它与方法的内容无关，但更多的是响应中使用的 XmlDocument 在哪里。如果您直接在响应中返回 XmlDocument(因此不是更大对象的一部分)，我可以通过添加一个知道如何反序列化(提取实际的 Xml 内容)的新 HttpContent 类型来修复它) XmlDocument。这是基于之前的 SO answer由 Darrel Miller 发布。这是一个完整的自包含示例中的修复。

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

常规代码修复

您的方法确实存在一些问题，因为它涉及到 SqlConnection。下面是清理后的代码和解释。

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

关于c# - 从存储过程中获取 XML，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/36603570/