Nilesh Avhad

Nilesh Avhad

  • NA
  • 600
  • 307.9k

require store procedure which accept XML File as i/p para

Sep 25 2013 6:09 AM
I have xml file as follows.

<?
xml version="1.0"?>
<catalog>
   <book id="bk101">
      <author>Gambardella, Matthew</author>
      <title>XML Developer's Guide</title>
      <genre>Computer</genre>
      <price>44.95</price>
      <publish_date>2000-10-01</publish_date>
      <description>An in-depth look at creating applications
      with XML.</description>
   </book>
   <book id="bk102">
      <author>Ralls, Kim</author>
      <title>Midnight Rain</title>
      <genre>Fantasy</genre>
      <price>5.95</price>
      <publish_date>2000-12-16</publish_date>
      <description>A former architect battles corporate zombies,
      an evil sorceress, and her own childhood to become queen
      of the world.</description>
   </book>
</catalog>

In my sql server 2008 databse there is table named Book__Master In which all the coloumns are same as elements in above xml file i.e. there is coloumn like bookid,authir, title,...etc.
Now I want to write a store procedure which accept this xml file as i/p parameter and stores that element's value as it is in their respective columns.
Can any one help me on this that how I can write this type of store procedure?

Answers (4)