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