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> 

Eskilstuna

What version of SQL Server are you using? – Wegner

Why do you need to do this work on the data in a stored procedure? Why not process the XML and make SQL requests separately? – Lubalubba

When you say "I tried this, but it doesn’t work", how do you know it doesn't work? What error message do you get? – Exculpate

Use new XmlNodeReader(xmlToSave.DocumentElement) and save serializing a potentially large document by taking XmlDocument.InnerXml. – Paramorphism

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)     

Reverse

Hi, how to reterive the details. <booksdetail> <isn_13>700001048</isbn_13> <isn_10>01048B</isbn_10> <Image_URL>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> – Eskilstuna

I've added some sample code to address your specific example. – Reverse

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

Bittersweet

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

Glorification

You should give an explanation on what your code is actually doing and how it solves the problem. Just pasting in some source code like this doesn't do a lot in regards to helping someone understand what specifically needs to be done. – Markman

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.

Amoeboid

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 )

Pettifog