Read Data from XML Parameter and Perform Insert /Update in SQL server

create table #temp

(

  ProductId int,

  PriceType int,

  Price decimal(18,2)

)

------------------Read XML Node's Attribute Value-------------------------

Declare @hdoc int

DECLARE @XmlData  varchar(max)='',@ProductId int=1,@XML xml

SET @XmlData='<Data><Item PriceType="1" Price="100" /><Item PriceType="2" Price="120" /><Item PriceType="3" Price="80" /></Data>'

SET @XML=CAST(@XmlData AS XML)

EXEC sp_xml_preparedocument @hdoc OUTPUT, @XML

--insert into #temp (ProductId,PriceType,Price)

SELECT @ProductId,x.PriceType,x.Price FROM OPENXML(@hdoc,'Data/Item')WITH

(

  PriceType Int '@PriceType',

  Price decimal(18,2) '@Price'

)x

EXEC sp_xml_removedocument @hdoc

select * from #temp

------------------Read XML Node Value----------------------------------

Declare @hdoc1 int

DECLARE @XmlData1  varchar(max)='',@ProductId1 int=1,@XML1 xml

SET @XmlData1='<Data><Items><Product>Hotel</Product><Price>100</Price></Items></Data>'

SET @XML1=CAST(@XmlData1 AS XML)

EXEC sp_xml_preparedocument @hdoc1 OUTPUT, @XML1

--insert into #temp (ProductId,PriceType,Price)

SELECT @ProductId1 'ProductId',x.Product,x.Price FROM OPENXML(@hdoc1,'Data/Items')WITH

(

  Product varchar(10) 'Product',

  Price decimal(18,2) 'Price'

)x

EXEC sp_xml_removedocument @hdoc1