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

Solution 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)     

Solution 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