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:

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))
    });

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>

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? — Brendan Enrick
– Brendan Enrick, Commented Aug 30, 2010 at 12:34
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? — Gabe
– Gabe, Commented Aug 30, 2010 at 12:40
Use new XmlNodeReader(xmlToSave.DocumentElement) and save serializing a potentially large document by taking XmlDocument.InnerXml. — escape-llc
– escape-llc, Commented Mar 7, 2017 at 13:07

Community · Accepted Answer · 2017-05-23 12:17:57Z

11

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)

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Aug 30, 2010 at 13:24

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Geeth Over a year ago

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>

Joe Stefanelli Over a year ago

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

Zafer · Accepted Answer · 2010-08-30 13:03:53Z

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

answered Aug 30, 2010 at 13:03

Zafer

2,17517 silver badges30 bronze badges

Comments

Oleks · Accepted Answer · 2012-04-14 05:32:12Z

1

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

edited Apr 14, 2012 at 5:32

Oleks

32.4k11 gold badges80 silver badges134 bronze badges

answered Apr 14, 2012 at 5:23

Jay

191 bronze badge

1 Comment

Johnathon Sullinger Over a year ago

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.

tster · Accepted Answer · 2010-08-30 12:34:23Z

0

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.

answered Aug 30, 2010 at 12:34

tster

18.3k6 gold badges57 silver badges72 bronze badges

Comments

Ravi Sharma · Accepted Answer · 2022-07-14 08:22:07Z

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 )

Collectives™ on Stack Overflow

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

5 Answers 5

2 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related