AngeloS AngeloS - 16 days ago 9
C# Question

How do I return XML from a Stored Procedure?

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

I'm having two issues. First, after doing some searching, it is not advised to use

.ExecuteScalar();
because it truncates strings over 2033 characters.

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"


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

Thank you in advance!!

Answer

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:

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

should solve the issue.