SQL Server: sp_xml_preparedocument not inserting/updating data in db from xml

Question

I have created a store procedure by which i am trying to insert data from xml. the data is not getting inserted or updated in db. i am not being able to capture the problem why data is not getting inserted. so please have look at my code with table structure and SP body and guide me what to change in code to work my SP properly.

Table Structure

CREATE TABLE [dbo].[TickerBrokerStandardDateLineitemValue] (
    [TabName]                 VARCHAR (MAX)   NULL,
    [StandardDate]            VARCHAR (MAX)   NULL,
    [XFundCode]               VARCHAR (MAX)   NULL,
    [BRTab]                   VARCHAR (MAX)   NULL,
    [BRLineItem]              VARCHAR (MAX)   NULL,
    [StandardLineItem]        VARCHAR (MAX)   NULL,
    [StandardValue]           DECIMAL (18, 2) NULL,
    [ActualProvidedByCompany] VARCHAR (MAX)   NULL,
    [ID]                      INT             IDENTITY (1, 1) NOT NULL,
    CONSTRAINT [PK_TickerBrokerStandardDateLineitemValue] PRIMARY KEY CLUSTERED ([ID] ASC)
);

Store Procedure

CREATE PROCEDURE [dbo].[uspInsertBoggyXmlData]
(
    @inputxml VARCHAR,
    @Status INT OUTPUT  
)

As 

Begin
    Declare @intCntr as INT
    Declare @intError as INT
    Declare @XMLFormat  as              INT

    Declare @TabName                    as Varchar(MAX)
    Declare @StandardDate               as Varchar(MAX)
    Declare @XFundCode                  as Varchar(MAX)
    Declare @BRTab                      as Varchar(MAX)
    Declare @BRLineItem                 as Varchar(MAX)
    Declare @StandardLineItem           as Varchar(MAX)
    Declare @StandardValue              as Varchar(MAX)
    Declare @ActualProvidedByCompany    as Varchar(MAX)


    Exec sp_xml_preparedocument @XMLFormat OUTPUT, @inputxml

    -- Create Cursor from XML Table

    Declare CurRecord
    Cursor For
    Select TabName,StandardDate,XFundCode,BRTab,BRLineItem,StandardLineItem,StandardValue,ActualProvidedByCompany
    From Openxml (@XMLFormat, '/TickerBrokerStandardDateLineitem/TickerBrokerStandardDateLineitemValues/TickerBrokerStandardDateLineitemValue', 3)
    With (
            TabName                 Varchar(MAX),
            StandardDate            Varchar(MAX),
            XFundCode               Varchar(MAX),
            BRTab                   Varchar(MAX),
            BRLineItem              Varchar(MAX),
            StandardLineItem        Varchar(MAX),
            StandardValue           Varchar(MAX),
            ActualProvidedByCompany Varchar(MAX)
         )


    BEGIN TRANSACTION   

    SET @Status=0
    SET @intCntr =0

    Open CurRecord
    Fetch Next From CurRecord Into @TabName,@StandardDate,@XFundCode,@BRTab,@BRLineItem,@StandardLineItem,@StandardValue,@ActualProvidedByCompany

    While (@@Fetch_Status=0)
    Begin

        IF(@StandardValue='')
        BEGIN
            SET @StandardValue = 0
        END

        SELECT @intCntr = COUNT(*) FROM TickerBrokerStandardDateLineitemValue 
            WHERE TabName=@TabName AND StandardDate=@StandardDate AND StandardLineItem=@StandardLineItem 

        IF(@intCntr=0)
        BEGIN
            INSERT INTO TickerBrokerStandardDateLineitemValue (TabName,StandardDate,XFundCode,BRTab,BRLineItem,StandardLineItem,StandardValue,ActualProvidedByCompany) 
            VALUES(@TabName,@StandardDate,@XFundCode,@BRTab,@BRLineItem,@StandardLineItem,CAST(@StandardValue AS DECIMAL),@ActualProvidedByCompany)  

        END
        ELSE
        BEGIN
            UPDATE TickerBrokerStandardDateLineitemValue
            SET XFundCode = @XFundCode,
            BRTab = @BRTab,
            BRLineItem = @BRLineItem,
            StandardValue = CAST(@StandardValue AS DECIMAL),
            ActualProvidedByCompany = @ActualProvidedByCompany
            WHERE TabName=@TabName AND StandardDate=@StandardDate AND StandardLineItem=@StandardLineItem  
        END


        SELECT @intError = @@error if @intError <> 0  Begin ROLLBACK TRANSACTION return  @intError End

        Fetch Next From CurRecord Into @TabName,@StandardDate,@XFundCode,@BRTab,@BRLineItem,@StandardLineItem,@StandardValue,@ActualProvidedByCompany
    End

    Close CurRecord
    Deallocate CurRecord


    select @intError = @@error if @intError <> 0  Begin ROLLBACK TRANSACTION return  @intError End
    SET @Status=1
    COMMIT TRANSACTION
    SELECT @Status
 END

This way i am calling SP

---

Declare @inputxml VARCHAR(MAX)  
 declare @Status int

 set @inputxml='<?xml version="1.0" encoding="utf-8"?>
 <TickerBrokerStandardDateLineitem>
   <Ticker />
   <TickerID />
   <TickerBrokerStandardDateLineitemValues>
     <TickerBrokerStandardDateLineitemValue>
       <TabName>CM Model101</TabName>
       <StandardDate>2010 FY</StandardDate>
       <XFundCode>TRIN0001</XFundCode>
       <BRTab></BRTab>
       <BRLineItem></BRLineItem>
       <StandardLineItem>Net Revenue</StandardLineItem>
       <StandardValue>1608.7</StandardValue>
       <ActualProvidedByCompany>NO</ActualProvidedByCompany>
     </TickerBrokerStandardDateLineitemValue>
     <TickerBrokerStandardDateLineitemValue>
       <TabName>JP Bank</TabName>
       <StandardDate>3Q 2018</StandardDate>
       <XFundCode>RD_015</XFundCode>
       <BRTab></BRTab>
       <BRLineItem></BRLineItem>
       <StandardLineItem>Days of Inventory</StandardLineItem>
       <StandardValue></StandardValue>
       <ActualProvidedByCompany>YES</ActualProvidedByCompany>
     </TickerBrokerStandardDateLineitemValue>
     <TickerBrokerStandardDateLineitemValue>
       <TabName>ZZZZZZZZZZZ</TabName>
       <StandardDate>3Q 2018</StandardDate>
       <XFundCode>RD_015</XFundCode>
       <BRTab></BRTab>
       <BRLineItem>991</BRLineItem>
       <StandardLineItem>Days of Inventory</StandardLineItem>
       <StandardValue>77.30</StandardValue>
       <ActualProvidedByCompany>YES</ActualProvidedByCompany>
     </TickerBrokerStandardDateLineitemValue>
   </TickerBrokerStandardDateLineitemValues>
 </TickerBrokerStandardDateLineitem>'

 EXEC [dbo].[uspInsertBoggyXmlData] @inputxml, @Status output

 Select @Status

Answer 1

Your first procedure could be filing or not returning anything. You can check that.

begin try
    begin tran
        Exec sp_xml_preparedocument @XMLFormat OUTPUT, @inputxml
    commit 
end try
begin catch
    if @@trancount > 0 rollback tran
    select ERROR_MESSAGE()
    ;throw
end catch

Then of course, before the rest of you code, you could check @XMLFormat to make sure it isn't NULL

Similarly, I would wrap your cursor attempts in their own transaction for each iteration with error handling. Erland Sommarskog has blogged extensively on error handling so I won't duplicate it here.