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

Question

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 bookidauthir 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?

Jignesh Trivedi · Answer

hi 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.

Nilesh Avhad · Answer

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) what is mean by T.N.value? will you please explain me?

Jignesh Trivedi · Answer

here i am passing hard coded data and stored in temp table Instead of this please replace this code with real parameters...

Jignesh Trivedi · Answer

hi try following code in your stored procedure create table #Book__Master(author varchar(100)title varchar(100)genre varchar(100)price floatpublish_date datedescription varchar(100)) declare @xml xml = ' Gambardella Matthew XML Developer''s Guide Computer 44.95 2000-10-01 An in-depth look at creating applications with XML. Ralls Kim Midnight Rain 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(authortitlegenrepricepublish_datedescription) 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.