Xml input in Sql Server Stored Procedure.

Input XML:

DECLARE    @xml XML

set @xml = '<SIPMst>

<Att Loc= "HOQC" WhID= "40458" Qty= "1" HOQCId= "49583" GRID= "0" StockID = "0" InvIDId= "0" Cmts= "sfsdsf" />

<Att Loc= "HOQC" WhID= "48863" Qty= "2" HOQCId= "60947" GRID= "0" StockID = "0" InvIDId= "0" Cmts= "" />

<Att Loc= "HOQC" WhID= "50111" Qty= "1" HOQCId= "62958" GRID= "0" StockID = "57183" InvIDId= "0" Cmts= "dfsfsf" />

<Att Loc= "HOQC" WhID= "211925" Qty= "1" HOQCId= "248278" GRID= "0" StockID = "30000014" InvIDId= "0" Cmts= "" />

</SIPMst>'

Create Temp Table :

declare @tmpSIdtl table

(

ID int IDENTITY(1,1) PRIMARY KEY,

Loc varchar(10),

WhID numeric(10,0),

Qty int,

HOQCId numeric(10,0),

GRID  numeric(10,0),

StockID numeric(10,0),

InvIDId varchar(50),

Cmts varchar(255)

)

Inserting Into temp Table

insert into @tmpSIdtl

SELECT  x.v.value('@Loc[1]', ' varchar(10)') AS [Loc],

x.v.value('@WhID[1]', ' numeric(10,0)') AS [WhID],

x.v.value('@Qty[1]', ' int') AS [Qty],

x.v.value('@HOQCId[1]', ' numeric(10,0)') AS [HOQCId],

x.v.value('@GRID[1]', ' numeric(10,0)') AS [GRID],

x.v.value('@StockID[1]', ' numeric(10,0)') AS [StockID],

x.v.value('@InvIDId[1]', ' varchar(50)') AS [InvIDId],

x.v.value('@Cmts[1]', ' varchar(255)') AS [Cmts]

FROM    @xml.nodes('/SIPMst/Att') x(v)

--select * from @tmpSIdtl

begin tran

/*

select T.ID,H.HOQCId,H.WhIntPrdQty,H.HOQCQtyDispatched,T.Qty from CbzHOQCManagement H

Join @tmpSIdtl T on T.HOQCId = H.HOQCId

and T.Loc = 'HOQC'

and (H.WhIntPrdQty-H.HOQCQtyDispatched) < T.Qty

*/

/*

Begin Transaction Updates in HOQC Table

*/

Delete @tmpSIdtl

from @tmpSIdtl T

inner Join dbo.CbzHOQCManagement H with(nolock) on T.HOQCId = H.HOQCId

and T.Loc = 'HOQC'

and (H.WhIntPrdQty-H.HOQCQtyDispatched) < T.Qty

set @Status = @@ERROR

--print @Status

if(@Status = 0)

begin

update dbo.CbzHOQCManagement set HOQCQtyDispatched = HOQCQtyDispatched + T.Qty

from dbo.CbzHOQCManagement H

inner Join @tmpSIdtl T on T.HOQCId = H.HOQCId

where T.Loc = 'HOQC'

set @Status = @@ERROR

end

if(@Status = 0)

begin

insert into dbo.cbzStockInputDisApproved

(Location,whMasterID,WhIntId,Qty,StockID,HOQCId,GRID,InvVarID,Comments,CreatedBy,CreatedDate,IsActive)

Select Loc,@whMasterID,WhID,Qty,StockID,HOQCId,GRID,InvIDId,Cmts,@ModifiedBy,@date,1

from @tmpSIdtl where Loc = 'HOQC'

set @Status = @@ERROR

end

if(@Status = 0)

begin

delete  from @tmpSIdtl where Loc = 'HOQC'

set @Status = @@ERROR

end

/*

END Transaction Updates in HOQC Table

*/

/*

Begin Transaction Updates in GR Table

*/

if(@Status = 0)

begin

Delete @tmpSIdtl

from @tmpSIdtl T

inner Join dbo.CbzGrStaging H on T.GRID = H.GRId

and T.Loc = 'GR'

and (H.WhIntPrdQty-H.GRQtyDispatched) < T.Qty

set @Status = @@ERROR

end

if(@Status = 0)

begin

update dbo.CbzGrStaging set GRQtyDispatched = GRQtyDispatched + T.Qty

from dbo.CbzGrStaging H

inner Join @tmpSIdtl T on T.GRID = H.GRId

where T.Loc = 'GR'

set @Status = @@ERROR

end

if(@Status = 0)

begin

insert into dbo.cbzStockInputDisApproved

(Location,whMasterID,WhIntId,Qty,StockID,HOQCId,GRID,InvVarID,Comments,CreatedBy,CreatedDate,IsActive)

Select Loc,@whMasterID,WhID,Qty,StockID,HOQCId,GRID,InvIDId,Cmts,@ModifiedBy,@date,1

from @tmpSIdtl where Loc = 'GR'

set @Status = @@ERROR

end

if(@Status = 0)

begin

delete  from @tmpSIdtl where Loc = 'GR'

set @Status = @@ERROR

end

/*

END Transaction Updates in GR Table

*/

/*

Begin Transaction Updates in WH Table

*/

if(@Status = 0)

begin

Delete @tmpSIdtl

from @tmpSIdtl T

inner Join dbo.cbzWareHouseDisApproved H on T.StockID = H.StockID

and T.Loc = 'WH'

and H.ModifiedBy is not null

set @Status = @@ERROR

end

if(@Status = 0)

begin

update dbo.cbzWareHouseDisApproved set ModifiedBy = @ModifiedBy,ModifiedDate=@date

from dbo.cbzWareHouseDisApproved H

inner Join @tmpSIdtl T on T.StockID = H.StockID

where T.Loc = 'WH'

set @Status = @@ERROR

end

if(@Status = 0)

begin

insert into dbo.cbzStockInputDisApproved

(Location,whMasterID,WhIntId,Qty,StockID,HOQCId,GRID,InvVarID,Comments,CreatedBy,CreatedDate,IsActive)

Select Loc,@whMasterID,WhID,Qty,StockID,HOQCId,GRID,InvIDId,Cmts,@ModifiedBy,@date,1

from @tmpSIdtl where Loc = 'WH'

set @Status = @@ERROR

end

if(@Status = 0)

begin

delete  from @tmpSIdtl where Loc = 'WH'

set @Status = @@ERROR

end

/*

END Transaction Updates in WH Table

*/

/*

Begin Transaction Updates in HOS Table

*/

if(@Status = 0)

begin

Delete @tmpSIdtl

from @tmpSIdtl T

inner Join dbo.cbzWareHouseDisApproved H on T.StockID = H.StockID

and T.Loc = 'HOS'

and H.ModifiedBy is not null

set @Status = @@ERROR

end

if(@Status = 0)

begin

update dbo.cbzWareHouseDisApproved set ModifiedBy = @ModifiedBy,ModifiedDate=@date

from dbo.cbzWareHouseDisApproved H

inner Join @tmpSIdtl T on T.StockID = H.StockID

where T.Loc = 'HOS'

set @Status = @@ERROR

end

if(@Status = 0)

begin

insert into dbo.cbzStockInputDisApproved

(Location,whMasterID,WhIntId,Qty,StockID,HOQCId,GRID,InvVarID,Comments,CreatedBy,CreatedDate,IsActive)

Select Loc,@whMasterID,WhID,Qty,StockID,HOQCId,GRID,InvIDId,Cmts,@ModifiedBy,@date,1

from @tmpSIdtl where Loc = 'HOS'

set @Status = @@ERROR

end

if(@Status = 0)

begin

delete  from @tmpSIdtl where Loc = 'HOS'

set @Status = @@ERROR

end