Read XML result from SQL using FOR XML in Web API C#

Question

I have a stored procedure that returns a table in XML format.At the end of the script I have

for xml auto

I tried to read it as below

var data = db.Database.SqlQuery<string>("exec spGetDataInXML").ToString();

But when I debug it data variable holds below value

exec spGetDataInXML

Answer 1

You created the query but haven't executed it. Database.SqlQuery<T> returns a IEnumerable<T> object that will execute the query when it is enumerated. Try

var q = db.Database.SqlQuery<string>("exec spGetDataInXML");
// execute the query
foreach (string s in q)
{
  // do something
}