Hi All,

I have one xml as input IN SQL SERVER i want to insert xml value into one sql table my xml is ,

<main>
<Data>
	<ID>CB08</Loca>
	<Details>
		<Itemcode>135</Itemcode>
		<Item>test</Item>
		
	</Details>
	<Details>                         
	        <temcode>136</Itemcode>
		<Item>test1</Item>
	</Details>
</Data>
</main>

MY problem is,when i execute the above xml, it has been insert only first itemcode(first row from first <details>)but second row itemcode not inserting. but i want to insert both rows into a tablemy query is ,

CREATE TABLE #temp
			(
			id VARCHAR(100),	
			ItemCode INT
											
	        )

			INSERT #temp
			SELECT	
					Data.value('(Location/text())[1]','VARCHAR(100)') AS Location,
					Data.value('(Details/ItemCode/text())[1]','INT') AS ItemCode								
				
			FROM     
					@XML.nodes('/main/Data')AS AdviceData (Data)

So please let me know how can i insert both rows into one table.

THANKS IN ADVANCE.