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

Question

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> 

Answer 1

For part 2 of your question, see my answer to Stored procedure: pass XML as an argument and INSERT (key/value pairs) for an example of how to use XML within a stored procedure.

EDIT: Sample code below is based on the specific example given in the comments.

declare @MyXML xml

set @MyXML = '<booksdetail> 
                  <isbn_13>700001048</isbn_13> 
                  <isbn_10>01048B</isbn_10> 
                  <Image_URL>http://www.landt.com/Books/large/00/70100048.jpg</Image_URL> 
                  <title>QUICK AND FLUPKE</title> 
                  <Description> PRANKS AND JOKES QUICK AND FLUPKE - CATASTROPHE QUICK AND FLUPKE </Description> 
              </booksdetail>'

select Book.detail.value('(isbn_13/text())[1]','varchar(100)') as isbn_13, 
       Book.detail.value('(isbn_10/text())[1]','varchar(100)') as isbn_10, 
       Book.detail.value('(Image_URL/text())[1]','varchar(100)') as Image_URL, 
       Book.detail.value('(title/text())[1]','varchar(100)') as title, 
       Book.detail.value('(Description/text())[1]','varchar(100)') as Description
    from @MyXML.nodes('/booksdetail') as Book(detail)     

Answer 2

As stated in http://support.microsoft.com/kb/555266, you need to pass xml data as NText.

You can query an XML variable as follows:

DECLARE @PeopleXml XML
    SET @PeopleXml = '<People>
    <Person>
    <Name>James</Name>
    <Age>28</Age>
    </Person>
    <Person>
    <Name>Jane</Name>
    <Age>24</Age>
    </Person>
    </People>'
--  put [1] at the end to ensure the path expression returns a singleton.
SELECT p.c.value('Person[1]/Name[1]', 'varchar(50)')
FROM @PeopleXml.nodes('People') p(c) -- table and column aliases

Answer 3

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...";
}

Answer 4

You will be using xPath and XQuery mostly to query and modify XML data.

Here is a good starting point http://msdn.microsoft.com/en-us/library/ms190798.aspx.

I can't really get any more specific because your question is extremely vague. Please ask specific questions on how to do something if you want help with how to use XPath and XQuery.

Answer 5

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 )