Procedure to insert bulk data from heirarchy XML

USE [OMS]
GO
/****** Object:  StoredProcedure [inv].[insertbulkphysicalInventoryold]    Script Date: 11/26/2014 10:09:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
------Create by preeti on 10-10-2014

--inv.insertphysicalInventory '
--<inv_feed>
--  <item>
--    <code>kba6_34</code>
--    <inv_detail>
--      <loc>DL</loc>
--      <bucket>
--        <name>PGRN</name>
--        <qty>20</qty>
--      </bucket>
--      <bucket>
--        <name>BSTK</name>
--        <qty>10</qty>
--      </bucket>
--      <bucket>
--        <name>CSMT</name>
--        <qty>50</qty>
--      </bucket>
--      <bucket>
--        <name>BSTK</name>
--        <qty>10</qty>
--      </bucket>
--    </inv_detail>
--    <inv_detail>
--      <loc>ST</loc>
--      <bucket>
--        <name>PGRN</name>
--        <qty>100</qty>
--      </bucket>
--      <bucket>
--        <name>BSTK</name>
--        <qty>80</qty>
--      </bucket>
--      <bucket>
--        <name>CSMT</name>
--        <qty>70</qty>
--      </bucket>
--      <bucket>
--        <name>BSTK</name>
--        <qty>10</qty>
--      </bucket>
--    </inv_detail>
 
--  </item>
--  <item>
--    <code>kcr3765_32</code>
--    <inv_detail>
--      <loc>DL</loc>
--      <bucket>
--        <name>PGRN</name>
--        <qty>20</qty>
--      </bucket>
--      <bucket>
--        <name>BSTK</name>
--        <qty>40</qty>
--      </bucket>
--      <bucket>
--        <name>CSMT</name>
--        <qty>60</qty>
--      </bucket>
--      <bucket>
--        <name>BSTK</name>
--        <qty>10</qty>
--      </bucket>
--    </inv_detail>
--    <inv_detail>
--      <loc>ST</loc>
--      <bucket>
--        <name>PGRN</name>
--        <qty>10</qty>
--      </bucket>
--      <bucket>
--        <name>BSTK</name>
--        <qty>20</qty>
--      </bucket>
--      <bucket>
--        <name>CSMT</name>
--        <qty>40</qty>
--      </bucket>
--      <bucket>
--        <name>BSTK</name>
--        <qty>10</qty>
--      </bucket>
--    </inv_detail>

--  </item>
--</inv_feed>
--'

ALTER Proc [inv].[insertbulkphysicalInventoryold]
@invfeedxml XML =null,
@userid varchar(10)=''
as begin
begin try
begin transaction
    begin
if(@invfeedxml is not null)
begin
delete from  inv.physicalstock
insert into inv.physicalstock(itemcode,loccode,bucketcode ,qty,disabled,dstatus,createdby,createddate)

SELECT
   CAST(CAST(x.item.query('data(code)')   AS  varchar(30))  AS varchar(30)),
  CAST(CAST(y.item.query('data(loc)')  AS  varchar(1000)) AS varchar(1000)),
    CAST(CAST(z.item.query('data(name)') AS char(25)) AS varchar(100)),
   CAST(z.item.query('data(qty)') AS varchar(25)),'false' as disabled,'false' as dstatus,@userid as createdby,GETDATE() as createdate
FROM
    @invfeedxml.nodes('/inv_feed/item') x(item)
    CROSS APPLY  
     x.item.nodes('./inv_detail') AS y(item)
  CROSS APPLY
     y.item.nodes('./bucket') AS z(item)
  end
 
  --select itemcode,loccode,bucketcode ,qty from inv.physicalstock
  end
    select 'successfully updated' as msg
commit transaction
end try
begin catch
rollback transaction
select 'not successfully updated' as msg
end catch
end