CREATE PROCEDURE
[dbo].[xmlProducts]
(
@XmlOverView NVARCHAR(MAX) = null
)
AS
BEGIN
/***********************************************************************************
** Desc :
SP to save Products details with XML documents
** Author :
Ramakrishna K
** Date :
06/08/2012
***********************************************************************************/
/***************************************
* Standard SET statements.
***************************************/
SET NOCOUNT ON
SET ANSI_PADDING ON
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER OFF
/***************************************
* Genric variables declaration
***************************************/
DECLARE
@Msg VARCHAR(1000), -- To display message text
@Error INT -- Captuer errors
SELECT
@Error = 0,
@Msg = ''
/***************************************
*
SP declared variables
***************************************/
DECLARE
@TransactionExists BIT,
@PID INT,
@PName NVARCHAR(100),
@iDoc INT
BEGIN TRY
--The number of
BEGIN TRANSACTION statements that have occurred on the current connection.
IF @@TRANCOUNT >
0
SET @TransactionExists =
1
ELSE
SET @TransactionExists =
0
IF
@TransactionExists = 0
BEGIN
--Initialise
transaction
BEGIN TRANSACTION
END
---------------Section
to save the product details into a table--------------------------
IF(@XmlOverView != NULL OR @XmlOverView != '')
BEGIN
EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @XmlOverView
INSERT INTO
Production.xmlTest
(PID
,PName
)
SELECT * FROM OPENXML(@iDoc, '/NewDataSet/Table',2)
WITH (ProductID INT
,Name NVARCHAR(500)
)
EXECUTE sp_xml_removedocument @iDoc
IF
@TransactionExists = 0
BEGIN
COMMIT TRANSACTION
RETURN (0)
END
END
END TRY
BEGIN CATCH
IF @TransactionExists =
0
ROLLBACK TRANSACTION
SET @msg = Error_Message()
RAISERROR (@msg,16,1) WITH NOWAIT
IF @Error = 0
SET @Error
= 1
RETURN (@Error)
END CATCH
END
|
