如何从存储过程返回XML ?

I created a Stored Procedure that returns XML and I would like to also return that XML in a method I created.

我创建了一个返回XML的存储过程，我还希望在创建的方法中返回该XML。

I'm having two issues. First, after doing some searching, it is not advised to use .ExecuteScalar(); because it truncates strings over 2033 characters.

我有两个问题。首先，在进行一些搜索之后，不建议使用.ExecuteScalar();因为它截断超过2033个字符的字符串。

So, I found a function called ExecuteXMlReader(), but in Visual Web Developer 2010 Express that runs on .NET 4.0 (C#) it is throwing the error "System.Data.SqlClient.SqlCommand' does not contain a definition for 'ExecuteXMlReader' and no extension method 'ExecuteXMlReader' accepting a first argument of type 'System.Data.SqlClient.SqlCommand' could be found"

因此，我找到了一个叫做ExecuteXMlReader()的函数，但是在Visual Web Developer 2010 Express中，它在。net 4.0 (c#)上运行，它抛出了错误的System.Data.SqlClient。SqlCommand'没有'ExecuteXMlReader'的定义，也没有'ExecuteXMlReader'的扩展方法'ExecuteXMlReader'接受'System.Data.SqlClient '类型的第一个参数。SqlCommand能找到”

Here is my stored procedure:

这是我的存储过程:

CREATE PROCEDURE dbo.GETReport
    (@ReportDate date)
AS
SELECT * FROM ReportTbl
WHERE ReportDate = @ReportDate
for xml auto, elements

set nocount on;

RETURN

Here is my method:

这是我的方法:

using System.Data;
using System.Data.SqlClient;

...

        //connect        
        SqlConnection conn = new SqlConnection("Data Source=localhost; User Id=foo; Password=foo; Initial Catalog=Database1");
        conn.Open();

        //create command
        SqlCommand cmd = new SqlCommand("dbo.GETReport", conn);
        cmd.Parameters.AddWithValue("@ReportDate", "3/24/2011"); 
        cmd.CommandType = CommandType.StoredProcedure;

        DataReader rd = cmd.ExecuteXMlReader(); //this is where error is occuring
        //also, it is throwing an error for DataReader as well saying there is no
        //type of namespace with that name
        rd.Read();

        string s = rd.ReadOuterXml(); //also dont know if this is how i should return the XML

Second, in addition to the ExecuteXMLReader() issue, I don't know if returning a string is the proper way of returning XML in the first place... Is there another object type I should convert it to?? Or another function I should use??

其次，除了ExecuteXMLReader()问题之外，我不知道首先返回字符串是否是返回XML的正确方式……是否有另一个对象类型需要我转换成?还是用另一个函数?

Thank you in advance!!

提前谢谢你! !

2 个解决方案

#1

First, SqlCommand has a ExecuteXmlReader method, not ExecuteXMlReader as you wrote (this is misspelling). Second, SqlCommand.ExecuteXmlReader method returns a value of type XmlReader, not a DataReader as is in your example. So changing your code to:

首先，SqlCommand有一个ExecuteXmlReader方法，而不是编写时的ExecuteXmlReader(这是拼写错误)。第二,SqlCommand。ExecuteXmlReader方法返回XmlReader类型的值，而不是您的示例中的DataReader。因此将代码更改为:

using (XmlReader reader = cmd.ExecuteXmlReader())
{
    while(reader.Read())
    {
        string s = reader.ReadOuterXml();
        // do something with s
    }
}

should solve the issue.

应该解决这个问题。

#2

I had trouble with the simple approach from @Alex and better luck with this approach:

我在@Alex的简单方法上遇到了麻烦，希望这种方法能给我带来好运:

// Execute a SqlCommand that you've created earlier.
// (Don't forget your 'using' statements around SqlConnection & SqlCommand!)
XmlReader xmlReader = cmd.ExecuteXmlReader();

// This is where our XML will end up 
var xmlDocument = new XmlDocument();

// Now xmlReader has the XML but no root element so we can't
// load it straight into XmlDocument :( But we can use XPathDocument
// to add a node for us first.
var xp = new XPathDocument(xmlReader);
var xn = xp.CreateNavigator();
XmlNode root = xmlDocument.CreateElement("YourFavouriteRootElementName");
root.InnerXml = xn.OuterXml;
xmlDocument.AppendChild(root);

// Now xmlDocument has all the XML you have dreamed of

Using the reader.Read() ... var s = reader.ReadOuterXml() somehow missed some of the elements in my longer more complex XML. I didn't bother investigating why but switching to XPathDocument worked for me.

使用reader.Read()……var s = reader.ReadOuterXml()在某种程度上漏掉了更复杂的XML中的一些元素。我没有去探究为什么，但是切换到XPathDocument对我很有效。

#1

using (XmlReader reader = cmd.ExecuteXmlReader())
{
    while(reader.Read())
    {
        string s = reader.ReadOuterXml();
        // do something with s
    }
}