sql解析xml

ALTER  PROCEDURE [dbo].[pt_OfficeApplyMulAdd]
(
@strXMLList text  --xml文件
)
AS
declare @hdoc int
SET XACT_ABORT ON
--BEGIN TRAN
 BEGIN
  exec sp_xml_preparedocument @hdoc output,@strXMLList
  select OffsID,OffsFlowID,OffsNumber,OffsAmount,OffsStoreID,SubName,SubCode,ApplyPurpose,OffsPrice,OID into # from openxml(@hdoc,'/root/record',2 )
   with (
      OffsID int,OffsFlowID int,OffsNumber varchar(200),OffsAmount int,OffsStoreID int,SubName varchar(30),SubCode varchar(20),ApplyPurpose varchar(200),OffsPrice numeric(10,2),OID int
   )
  insert into OfficeApply ( OffsID,OffsFlowID,OffsNumber,OffsAmount,OffsStoreID,SubName,SubCode,ApplyPurpose,OffsPrice,OID)  select  OffsID,OffsFlowID,OffsNumber,OffsAmount,OffsStoreID,SubName,SubCode,ApplyPurpose,OffsPrice,OID from #
truncate table #
  drop table # 
     exec sp_xml_removedocument @hdoc 
 END
--COMMIT TRAN
go