SET NOCOUNT ON;
DECLARE @BankXml VARCHAR(MAX) = '<ROOT><ITEM BAF="HI" /></ROOT>'
DECLARE @ErrMsg VARCHAR(MAX) ='',@XmlId INT,@TranCount INT
CREATE TABLE #tmptbl(BAF VARCHAR(10))
IF (@BankXml IS NOT NULL)
BEGIN
EXEC SP_XML_PREPAREDOCUMENT @XmlId OUTPUT, @BankXml
INSERT INTO #tmptbl(BAF)
SELECT BAF
FROM OPENXML(@XmlId, 'ROOT/ITEM', 1) WITH
(
BAF VARCHAR(10)
)
END
BEGIN TRY
IF @@TRANCOUNT = 0
SET @TranCount = 1
IF @TranCount=1
BEGIN TRAN
IF 1=1
BEGIN
SELECT BAF FROM #tmptbl
END
IF @TranCount = 1
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT = 1 AND @TranCount = 1
ROLLBACK TRAN
SET @ErrMsg = 'Error : ' + @ErrMsg + ' : ' + ERROR_MESSAGE()
RAISERROR(@ErrMsg,16,1)
END CATCH
DECLARE @BankXml VARCHAR(MAX) = '<ROOT><ITEM BAF="HI" /></ROOT>'
DECLARE @ErrMsg VARCHAR(MAX) ='',@XmlId INT,@TranCount INT
CREATE TABLE #tmptbl(BAF VARCHAR(10))
IF (@BankXml IS NOT NULL)
BEGIN
EXEC SP_XML_PREPAREDOCUMENT @XmlId OUTPUT, @BankXml
INSERT INTO #tmptbl(BAF)
SELECT BAF
FROM OPENXML(@XmlId, 'ROOT/ITEM', 1) WITH
(
BAF VARCHAR(10)
)
END
BEGIN TRY
IF @@TRANCOUNT = 0
SET @TranCount = 1
IF @TranCount=1
BEGIN TRAN
IF 1=1
BEGIN
SELECT BAF FROM #tmptbl
END
IF @TranCount = 1
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT = 1 AND @TranCount = 1
ROLLBACK TRAN
SET @ErrMsg = 'Error : ' + @ErrMsg + ' : ' + ERROR_MESSAGE()
RAISERROR(@ErrMsg,16,1)
END CATCH
No comments:
Post a Comment