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

Answer 2

As mentioned by shA.t "FOR XML" can be used. However one thing to take care of while using is the truncation of string/XML (returned after function call via EF) at around 2k characters, to handle this scenario you can have a look at this. Also if the code design allows, you can even use Ouptput parameters with Enitity Framework.

Answer 3

Database.ExecuteSqlCommand executes commands used for CRUD operation, not querying.

Using Database.SqlQuery is for queries. It will return elements of a given type but xml isn't a primitive type and probably it's a reason why LINQ not working. Try cast xml in stored procedure to nvarchar(max) this will be string type.

. So your stored procedure should look like:

    CREATE PROCEDURE xml_test    
AS
BEGIN
    DECLARE @xml1 xml

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

    SELECT CAST(@xml1 as nvarchar(max))
END