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


I have xml file as above.

In my SQL Server 2008 database there is table Book__Master In which all the columns are same as elements in above xml file i.e. there are columns like bookid, author, title etc. 

Now I want to write a stored procedure which accepts this xml file as input parameter and stores the element's values as it is in their respective columns.

Can anyone help me on this that how I can write this type of stored procedure?
You can use X-Query for that:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE PROCEDURE dbo.ParseXML
   @XML XML
AS
BEGIN

  SELECT 
    Book.value('@id','NVARCHAR(100)') AS id,
    Book.value('author[1]','NVARCHAR(100)') AS author,
    Book.value('title[1]','NVARCHAR(100)') AS title,
    Book.value('price[1]','DECIMAL(10,4)') AS price,
    Book.value('publish_date[1]','DATE') AS publish_date

    FROM @XML.nodes('catalog/book')Catalog(Book)

RETURN;
END;


Query 1:

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

EXEC dbo.ParseXML @FileXML;


Results:

|    ID |               AUTHOR |                 TITLE | PRICE | PUBLISH_DATE |
|-------|----------------------|-----------------------|-------|--------------|
| bk101 | Gambardella, Matthew | XML Developer's Guide | 44.95 |   2000-10-01 |
| bk102 |           Ralls, Kim |         Midnight Rain |  5.95 |   2000-12-16 |


This shows you how to get to the values. From there you can easily insert them into your table.