none
How to convert stored procedure call result to System.Xml.XmlDocument RRS feed

  • Question

  • I am trying to call a stored procedure from LINQ to SQL.

    The stored procedure returns XML.

    The question is how to cast ISingleResult to System.Xml.XmlDocument data type.

    I am getting the following error message:

    Unable to cast object of type 'SingleResult` … to type 'System.Xml.XmlDocument'.


    Stored Procedure:

    ======================

    SELECT

    FROM

    FOR XML AUTO, ROOT('root');

     

    LINQ to SQL code

    ===============================================

    DataClassesDataContext db = DataManager.AppDB;

    var RawXML = db.usp_spname();

     

    XmlDocument xmlDoc = new XmlDocument();

    xmlDoc = (System.Xml.XmlDocument)RawXML;


    Regards,
    Yitzhak


    Monday, September 8, 2008 6:20 PM

Answers

  • Here is the answer.
    LINQ to SQL returns XML from the stored procedure containing the SELECT ... FOR XML clause by chunks.
    So the solution is to loop through all the chunks, concatenating them to a temporary string variable.
    The code below demonstrates how it works.


    string strXML = "";

    DataClassesDataContext db = DataManager.AppDB;


    var RawXML = db.usp_spname();
    foreach(usp_spnameResult xmlitem in RawXML)
    {
          strXML = strXML + xmlitem.XML_F52E2B61_18A1_11d1_B105_00805F49916B;
    }

    XmlDocument xmlDoc = new XmlDocument();
    xmlDoc.LoadXml(strXML);

    --
    Regards,
    Yitzhak
    Tuesday, September 9, 2008 2:04 PM