/*
*功能:SQL批量信息录入
*此存储过程获取表单信息,插入表中。
*/
CREATE PROC [dbo].[sp_SaveToMX1]
@XML text --明细表XML字符串信息
AS
BEGIN
declare @newXMLDoc int= 1
BEGIN TRANSACTION
EXEC sp_xml_preparedocument @newXMLDoc OUTPUT, @XML
--将明细表分录信息插入MX1表中(多条,来自XML字符串,即MX1)
insert into MX1(BXID,kmID,KMMC)
SELECT AusrottenID,WriteOffID,WriteOffSort FROM OPENXML(@newXMLDoc, '/root/record',2)
WITH
(
AusrottenID nvarchar(20),
WriteOffID nvarchar(20),
WriteOffSort nvarchar(50)
)
EXEC sp_xml_removedocument @newXMLDoc --明细表分录信息插入成功后,移除该对象
IF @@ERROR<>0
ROLLBACK TRANSACTION
COMMIT TRANSACTION
END
GO