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


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.


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

        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;
              new SqlParameter("@xml", SqlDbType.Xml)
                  Value = new SqlXml(new XmlTextReader(xmlToSave.InnerXml
                             , XmlNodeType.Document, null))

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

            catch (Exception)

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>       
    <title>QUICK AND FLUPKE</title> <Description> PRANKS AND JOKES QUICK AND FLUPKE </Description> </booksdetail> 


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> 
                  <title>QUICK AND FLUPKE</title> 
                  <Description> PRANKS AND JOKES QUICK AND FLUPKE - CATASTROPHE QUICK AND FLUPKE </Description> 

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)     

Answered By – Joe Stefanelli

Answer Checked By – Robin (BugsFixing Admin)

Leave a Reply

Your email address will not be published.