Linq stored procedure to return XML

Question

I am using Entity Framework code-first approach. I want to call a stored procedure from the DbContext class and to get XML output.

Stored procedure (SQL Server):

CREATE PROCEDURE xml_test    
AS
BEGIN
    DECLARE @xml1 xml

    SET @xml1 = (SELECT * from Product FOR XML RAW) 

    SELECT @xml1 AS my_xml
END

LINQ Entity Framework:

using (DBContext db = new DBContext())
{
    var ProductList = await db.Database.ExecuteSqlCommandAsync("exec xml_test");
}

Here the ProductList list is returning -1.

I want to get the xml output which is returned by the stored procedure.

Note: I have also tried methods like: ExecuteSqlCommand, SqlQuery with no help.

Answer 1

I think you can use SQLQuery like this:

using (var dbcontext = new DBContext())
{
    //Reading stored procedure results as List<string>
    var r = dbcontext.Database.SqlQuery<string>("EXEC xml_test").ToList(); //Note: EXEC is optional

    //Joining strings to one string that causes in resulting long strings
    var xmlString = string.Join("", r);

    //Now you can load your string to a XmlDocument
    var xml = new XmlDocument();

    //Note: You need to add a root element to your result
    xml.LoadXml($"<root>{xmlString}</root>");
}

Note: To get records from your stored procedure you need to add SET NOCOUNT ON; after BEGIN ;).

CREATE PROCEDURE [dbo].[xml_test] 
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * from dbo.AspNetUsers FOR XML RAW;
END