XML Sqlquery

USE [CIPDB]
GO
/****** Object:  StoredProcedure [dbo].[uspAddProgramCategory]    Script Date: 02/08/2011 18:44:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[upsAddProgramCategory]
(

/* Serialized DataXML*/
    @xmlData XML      
 )        
AS
/* Try Statement Begins */
/*BEGIN TRY
BEGIN TRANSACTION */
/* This Insert Statement populates a CIPProgram Table*/

DECLARE @xmlPointer INT  -- Pointer variable
EXEC sp_xml_preparedocument @xmlPointer OUTPUT,
                            @xmlData  -- Get XML document into memory
DECLARE @ProgramID uniqueidentifier
DECLARE @CategoryID uniqueidentifier
DECLARE @err AS INT
   
BEGIN TRY                       
BEGIN TRANSACTION

INSERT INTO dbo.LocalizationMaster
(
    StringID
)
SELECT
StringID
FROM OPENXML (@xmlPointer,'/ProgramCategory',1)
WITH(StringID uniqueidentifier 'StringID')

SELECT @err = @@error
IF @err <> 0
  BEGIN
        ROLLBACK TRANSACTION
        RAISERROR ('An error occured',16,1)
        RETURN @err
END

SELECT @ProgramID = ProgramID ,@CategoryID = CategoryID
    FROM OPENXML (@xmlPointer,'/ProgramCategory',1)
    WITH(
        ProgramID uniqueidentifier 'ProgramID',
        CategoryID uniqueidentifier 'CategoryAttribute/CategoryID'   
        )
Print '1'
INSERT INTO ProgramCategory 
           (
             [ProgramID],
             [CategoryID],
             [StringID],
             [DefaultLanguageID]            
            )
     SELECT
             ProgramID,
             CategoryID,
             StringID,
             'cca5bd8d-172f-4687-9d58-607f0b235192'

FROM OPENXML (@xmlPointer,'/ProgramCategory',1)
WITH(
    ProgramID uniqueidentifier 'ProgramID',
    CategoryID uniqueidentifier 'CategoryAttribute/CategoryID',
    StringID uniqueidentifier 'StringID'
    )

SELECT @err = @@error
IF @err <> 0
  BEGIN
        ROLLBACK TRANSACTION
        RAISERROR ('An error occured while adding the Program',16,1)
        RETURN @err
END

Print '2'       
INSERT INTO LocalizedString
            (
                [LocalizedStringID],
                [PlatformLanguageID],
                [LocalizedString],
                [StringID]
            )
        SELECT
                LocalizedStringID,
                PlatformLanguageID,
                LocalizedString,
                StringID
               
FROM OPENXML (@xmlPointer,'/ProgramCategory/ProgramCategoryName',1)
WITH(
    LocalizedStringID uniqueidentifier 'LocalizedStringID',
    PlatformLanguageID uniqueidentifier 'PlatformLanguageID',
    StringID uniqueidentifier 'StringID',
    LocalizedString varchar(max) 'LocalizedString'
    )

Print '3'
SELECT @err = @@error
IF @err <> 0
  BEGIN
        ROLLBACK TRANSACTION
        RAISERROR ('An error occured while adding LocalizedString',16,1)
        RETURN @err
END


SELECT       
        AttributeID,
        StringID,
        DefaultLanguageID,
        Required,
        LocalizedStringID,
        PlatformLanguageID,
        LocalizedString       
INTO #AttributeDetails               
FROM OPENXML (@xmlPointer,'/ProgramCategory/Attributes/Attribute',1)
WITH(   
    AttributeID uniqueidentifier 'AttributeID',
    StringID uniqueidentifier 'StringID',
    DefaultLanguageID uniqueidentifier 'DefaultLanguageID',
    Required bit 'Available',
    LocalizedStringID uniqueidentifier 'ProgramAttributeName/LocalizedStringID',
    PlatformLanguageID uniqueidentifier 'ProgramAttributeName/PlatformLanguageID',   
    LocalizedString varchar(max) 'ProgramAttributeName/LocalizedString'
    )
Print '4'

INSERT INTO dbo.LocalizationMaster
(
    StringID
)
SELECT    #AttributeDetails.StringID
FROM #AttributeDetails

Print '5'       

select * from #AttributeDetails

    SELECT
            @ProgramID,
            categoryAttribute.CategoryAttributeID,
            --#AttributeDetails.ProgramCategoryAttributeID,
            #AttributeDetails.Required,
            #AttributeDetails.StringID
    FROM #AttributeDetails , CategoryAttribute categoryAttribute
    WHERE categoryAttribute.AttributeID = #AttributeDetails.AttributeID
        AND categoryAttribute.CategoryID = @CategoryID

INSERT INTO ProgramCategoryAttribute(
            ProgramID,
            CategoryAttributeID,
            Required,
            StringID)
    SELECT
            @ProgramID,
            categoryAttribute.CategoryAttributeID,            
            #AttributeDetails.Required,
            #AttributeDetails.StringID
    FROM #AttributeDetails , CategoryAttribute categoryAttribute
    WHERE categoryAttribute.AttributeID = #AttributeDetails.AttributeID
        AND categoryAttribute.CategoryID = @CategoryID
       
Print '6'
SELECT @err = @@error
IF @err <> 0
  BEGIN
        ROLLBACK TRANSACTION
        RAISERROR ('An error occured while adding to ProgramCategoryAttribute',16,1)
        RETURN @err
END

Print '7'
INSERT INTO LocalizedString
            (
                [LocalizedStringID],
                [PlatformLanguageID],
                [LocalizedString],
                [StringID]
            )
        SELECT
                #AttributeDetails.LocalizedStringID,
                #AttributeDetails.PlatformLanguageID,
                #AttributeDetails.LocalizedString,
                #AttributeDetails.StringID
        FROM #AttributeDetails
       
Print '8'
SELECT @err = @@error
IF @err <> 0
  BEGIN
        ROLLBACK TRANSACTION
        RAISERROR ('An error occured while adding to ProgramCategoryAttribute',16,1)
        RETURN @err
END


COMMIT TRANSACTION
SELECT @err = @@error
IF @err <> 0
  BEGIN
        ROLLBACK TRANSACTION
        RAISERROR ('Caught exception An error occured',16,1)
        RETURN @err
END
END TRY

/* Catch Begins */
BEGIN CATCH       
        SELECT @err = @@error
        IF @err <> 0
            BEGIN
                ROLLBACK TRANSACTION
                RAISERROR ('Caught exception An error occured',16,1)
                RETURN @err
        END
/* Catch Ends */
END CATCH