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

In SQL server, xml data type has method called node(), with the help of this method we can share an xml datatype instance into relational data. It allowus to identify the node and map it to new row.

The result of node function is rowset...
here T.N is nothing but rowset and we are fatch data from each row and column..

Please refer
http://technet.microsoft.com/en-us/library/ms188282.aspx

hope this will help you.

create table #Book__Master(author varchar(100),title varchar(100),genre varchar(100),price float,publish_date date,description varchar(100))

declare @xml xml = '

  
      Gambardella, Matthew
     
      Computer
      44.95
      2000-10-01
      An in-depth look at creating applications
      with XML.
  
  
      Ralls, Kim
     
      Fantasy
      5.95
      2000-12-16
      A former architect battles corporate zombies,
      an evil sorceress, and her own childhood to become queen
      of the world.
  
'

Insert into #Book__Master(author,title,genre,price,publish_date,description)
select T.N.value('author[1]', 'varchar(100)') as author,
T.N.value('title[1]', 'varchar(100)') as title,
T.N.value('genre[1]', 'varchar(100)') as genre,
T.N.value('price[1]', 'float') as price,
T.N.value('publish_date[1]', 'date') as publish_date,
T.N.value('description[1]', 'varchar(100)') as description
from @XML.nodes('/catalog/book') as T(N)

hope this will help you.