How to pass XML from C# to a stored procedure in SQL Server 2008?

user_1470997 0
Viewed 67520

I want to pass xml document to sql server stored procedure such as this:

CREATE PROCEDURE BookDetails_Insert (@xml xml)

I want compare some field data with other table data and if it is matching that records has to inserted in to the table.

Requirements:

  1. How do I pass XML to the stored procedure? I tried this, but it doesn’t work:[Working]

    command.Parameters.Add(
    new SqlParameter("@xml", SqlDbType.Xml)
    {
        Value = new SqlXml(new XmlTextReader(xmlToSave.InnerXml,
                           XmlNodeType.Document, null))
    });

  2. How do I access the XML data within the stored procedure?

Edit: [Working]

 String sql = "BookDetails_Insert";
        XmlDocument xmlToSave = new XmlDocument();
        xmlToSave.Load("C:\\Documents and Settings\\Desktop\\XML_Report\\Books_1.xml");

        SqlConnection sqlCon = new SqlConnection("...");
        using (DbCommand command = sqlCon.CreateCommand())
        {
            **command.CommandType = CommandType.StoredProcedure;**
            command.CommandText = sql;
            command.Parameters.Add(
              new SqlParameter("@xml", SqlDbType.Xml)
              {
                  Value = new SqlXml(new XmlTextReader(xmlToSave.InnerXml
                             , XmlNodeType.Document, null))
              });

            sqlCon.Open();
            DbTransaction trans = sqlCon.BeginTransaction();
            command.Transaction = trans;

            try
            {
                command.ExecuteNonQuery();
                trans.Commit();
                sqlCon.Close();
            }
            catch (Exception)
            {
                trans.Rollback();
                sqlCon.Close();
                throw;
            }

Edit 2: How to create a select query to select pages, description based on some conditions.

  <booksdetail> <isn_13>700001048</isbn_13> <isn_10>01048B</isbn_10>       
    <Image_URL>http://www.landt.com/Books/large/00/7010000048.jpg</Image_URL>   
    <title>QUICK AND FLUPKE</title> <Description> PRANKS AND JOKES QUICK AND FLUPKE </Description> </booksdetail>
sqlparameter stored-procedures xml sql-server c#
5 Answers
public static string UpdateStaticCertificateFormateNo1Data(StaticCertificateFormatNo1LogicLayer StaticFormat1Detail)
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
    con.Open();
    string strXMLRegistrationDetails, strXMLQutPut = "<root></root>";
    System.Xml.Serialization.XmlSerializer x = new System.Xml.Serialization.XmlSerializer(StaticFormat1Detail.GetType());
    System.IO.MemoryStream stream = new System.IO.MemoryStream();
    x.Serialize(stream, StaticFormat1Detail);
    stream.Position = 0;
    XmlDocument xd = new XmlDocument();
    xd.Load(stream);
    strXMLRegistrationDetails = xd.InnerXml;
    SqlTransaction trn = con.BeginTransaction();
    try
    {
        SqlParameter[] paramsToStore = new SqlParameter[2];
        paramsToStore[0] = ControllersHelper.GetSqlParameter("@StaticFormat1Detail", strXMLRegistrationDetails, SqlDbType.VarChar);
        paramsToStore[1] = ControllersHelper.GetSqlParameter("@OutPut", strXMLQutPut, SqlDbType.VarChar);
        SqlHelper.ExecuteNonQuery(trn, CommandType.StoredProcedure, "UPS_UpdateStaticCertificateFormateNo1Detail", paramsToStore);
        trn.Commit();
    }
    catch (Exception ex)
    {
        trn.Rollback();
        con.Close();
        if (ex.Message.Contains("UNIQUE KEY constrastring"))
        { return "Details already in  List"; }
        else { return ex.Message; }
    }
    con.Close();
    return "Details successfully Added...";
}
user_13328930 
var MainXML = new XElement("DocumentElement");
            foreach (GridViewRow gvr in gvStudent.Rows)
            {
                DropDownList ddl = (DropDownList)gvr.FindControl("ddlStudent");
                if (ddl.SelectedValue != "Select")
                {
                    string StuId = ((HiddenField)gvr.FindControl("hfStudentId")).Value;
                    var datacontent =
                                    new XElement("StudentStatus",
                                    new XElement("Status", ddl.SelectedValue),
                                    new XElement("StudentId", StuId)
                                    );
                    MainXML.Add(datacontent);
                }
            }
            if (MainXML.ToString() == "<DocumentElement />")
            {
                return;
            }
            string msg = obj.UpdateStudentProjectStatus(MainXML.ToString());
            
            
            --------------------------Sql--------------------------
            DECLARE
            @XMLData XML
                IF OBJECT_ID('tempdb..#tmpSelectionParameters') IS NOT NULL     --Remove dbo here 
                         DROP TABLE #tmpSelectionParameters   -- Remove "tempdb.dbo"

                SELECT   
                CAST(colx.query('data(Status)') AS varchar(500)) AS Status,
                CAST(colx.query('data(StudentId)') AS varchar(500)) AS StudentId 
                INTO    #tmpSelectionParameters
                FROM    @XMLData.nodes('DocumentElement/StudentStatus') AS Tabx ( Colx )
user_110277070
Related