How do I return XML from a Stored Procedure?
Solution 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:
using (XmlReader reader = cmd.ExecuteXmlReader())
{
while(reader.Read())
{
string s = reader.ReadOuterXml();
// do something with s
}
}
should solve the issue.
Solution 2
I had trouble with the simple approach from @Alex and better luck with this approach:
// Execute a SqlCommand that you've created earlier.
// (Don't forget your 'using' statements around SqlConnection, SqlCommand and XmlReader!)
// This is where our XML will end up
var xmlDocument = new XmlDocument();
using (XmlReader xmlReader = cmd.ExecuteXmlReader())
{
// 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.
Comments
-
AngeloS over 3 years
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!!
-
AngeloS about 13 yearsthat worked great, but quick question.. is there a way to return the XML as XML and not a string?
-
John Saunders about 13 yearsThe
XmlReader
is the underlying way to read any XML. You would use it to load an XDocument or XmlDocument, using XDocument.Load(reader), for instance. -
Oleks about 13 years@Angelo, you could use something similar to
XmlDocument document = new XmlDocument(); document.Load(reader);
or useXDocument.Load(reader)
as @John mentioned. Thanks, @John! -
AngeloS about 13 yearsWhen I use
XmlReader reader = cmd.ExecuteXmlReader(); XmlDocument document = new XmlDocument(); document.Load(reader);
I get the following error: "This document already has a 'DocumentElement' node." .. any ideas??