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

by

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)

More Related Contents:

  1. An unhandled exception of type ‘System.InvalidOperationException’ occurred in System.Data.dll?
  2. Procedure or function xxxxx has too many arguments specified [duplicate]
  3. How to pass an array into a SQL Server stored procedure
  4. How to execute a stored procedure within C# program
  5. How to pass table value parameters to stored procedure from .net code
  6. What are the pros and cons to keeping SQL in Stored Procs versus Code [closed]
  7. Call a stored procedure with parameter in c#
  8. Calling stored procedure with return value
  9. Using stored procedure output parameters in C#
  10. Using StringWriter for XML Serialization
  11. Getting data from stored procedure with Entity Framework
  12. Passing List to SQL Stored Procedure
  13. Using DateTime in a SqlParameter for Stored Procedure, format error
  14. SqlParameter does not allows Table name – other options without sql injection attack?
  15. WHERE IN (array of IDs)
  16. What is passing parameters to SQL and why do I need it?
  17. Add WHERE clauses to SQL dynamically / programmatically
  18. How to pass a null variable to a SQL Stored Procedure from C#.net code
  19. Why does the SqlParameter name/value constructor treat 0 as null?
  20. How do I return XML from a Stored Procedure?
  21. How to Read XML in .NET?
  22. DataContract XML serialization and XML attributes
  23. XPATHS and Default Namespaces
  24. Convert an object to an XML string
  25. Deserialize object property with StringReader vs XmlNodeReader
  26. How can I get started making a C# RSS Reader?
  27. Use SQL Server time datatype in C#.NET application?
  28. Force XDocument to write to String with UTF-8 encoding
  29. How to remove whitespace from an XmlDocument
  30. Is SqlConnection.Close() need inside a Using statement?

Leave a Comment