-- 开始更新数据的事务
SELECT @SendTime=SendTime
FROM OpenXML(@idoc, '/ArchiveMsg')
WITH ArchiveMsg
INSERT INTO ArchiveMsg(Title,UserID,Author,SendTime,Department,ReceiveList,Body,AttachFile)
SELECT Title,UserID,Author,SendTime,Department,ReceiveList,Body,AttachFile
FROM OpenXML(@idoc, '/ArchiveMsg')
WITH ArchiveMsg
-- 错误检查
SELECT @CurrentError = @@Error
IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END
SELECT @MsgID=@@IDENTITY
INSERT INTO MsgList(MsgID,UserID,SendTime)
SELECT @MsgID,UserID,@SendTime
FROM OpenXML(@idoc, '/ArchiveMsg/MsgList')
WITH (
MsgID int,
UserID varchar(80),
SendTime datetime
)
-- 错误检查
SELECT @CurrentError = @@Error
IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END
-- 事务结束
COMMIT TRANSACTION
SET NOCOUNT OFF
-- 从内存中移除定义的XML文档
EXEC sp_xml_removedocument @idoc
RETURN 0
ERROR_HANDLER:
ROLLBACK TRANSACTION
SET NOCOUNT OFF
-- 从内存中移除定义的XML文档
EXEC sp_xml_removedocument @idoc
RETURN @CurrentError
*******************************************************************************
好了,存储过程就是这样了,我们得测试这个存储过程,测试函数代码如下:
首先我们得生成这个XML文档 上一页 [1] [2] [3] [4] 下一页 [Sql Server]使用Microsoft SQL Server 2000的XML查询 [Sql Server]sqlserver2000的jdbc驱动和PreparedStatement的性…
|