UFN_REVENUEUPDATEBATCH_GETSOLICITORSXML

CREATE function dbo.UFN_REVENUEUPDATEBATCH_GETSOLICITORSXML
(
    @BATCHREVENUEID uniqueidentifier,
    @APPLICATIONSOLICITORS xml
)
returns xml
with execute as caller
as begin
    declare @APPLICATIONID uniqueidentifier
    declare @APPLICATIONCODE tinyint
    declare @DESIGNATIONID uniqueidentifier
    declare @DECLINESGIFTAID bit
   declare @SPONSORSHIPID uniqueidentifier
    declare @SOLICITORS xml
    declare @SOLICITORSTABLE table
    (
     APPLICATIONCODE tinyint,
     DESIGNATIONID uniqueidentifier,
     APPLICATIONID uniqueidentifier,
     SOLICITORS xml,
     DECLINESGIFTAID bit,
    SPONSORSHIPID uniqueidentifier
    )
    declare @SOLICITOR table
    (
        ID uniqueidentifier,
        CONSTITUENTID uniqueidentifier,
        AMOUNT money, 
        SEQUENCE integer,
        APPLICATIONID uniqueidentifier,
        REVENUESOLICITORID uniqueidentifier,
        APPLICATIONCODE tinyint,
        DESIGNATIONID uniqueidentifier,
        DECLINESGIFTAID bit,
      SPONSORSHIPID uniqueidentifier
    )

    insert into @SOLICITORSTABLE(APPLICATIONCODE, DESIGNATIONID, APPLICATIONID, SOLICITORS, DECLINESGIFTAID, SPONSORSHIPID)
    select T.c.value('(ADDITIONALAPPLICATIONTYPECODE)[1]','tinyint') as APPLICATIONCODE,
           T.c.value('(ADDITIONALAPPLICATIONDESIGNATIONID)[1]','uniqueidentifier') as DESIGNATIONID,
           T.c.value('(APPLICATIONID)[1]','uniqueidentifier') as APPLICATIONID,
           case when T.c.exist('./SOLICITORS/ITEM') = 1 then T.c.query('(SOLICITORS)[1]') else null end as SOLICITORS,
           T.c.value('(ADDITIONALAPPLICATIONDECLINESGIFTAID)[1]','bit') as DECLINESGIFTAID,
         T.c.value('(ADDITIONALAPPLICATIONSPONSORSHIPID)[1]','uniqueidentifier') as SPONSORSHIPID
    from @APPLICATIONSOLICITORS.nodes('/APPLICATIONSOLICITORS/ITEM') T(c)

    declare SOLICITORSCURSOR cursor local fast_forward for
    select ST.APPLICATIONCODE, ST.DESIGNATIONID, ST.APPLICATIONID, ST.SOLICITORS, ST.DECLINESGIFTAID, ST.SPONSORSHIPID
    from @SOLICITORSTABLE ST

    open SOLICITORSCURSOR

    fetch next from SOLICITORSCURSOR into @APPLICATIONCODE, @DESIGNATIONID, @APPLICATIONID, @SOLICITORS, @DECLINESGIFTAID, @SPONSORSHIPID
    while @@FETCH_STATUS = 0
    begin
        insert into @SOLICITOR (ID, CONSTITUENTID, AMOUNT, SEQUENCE, REVENUESOLICITORID, APPLICATIONID, APPLICATIONCODE, DESIGNATIONID, DECLINESGIFTAID, SPONSORSHIPID)
        select T.c.value('(ID)[1]','uniqueidentifier') as 'ID', 
        T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') as 'CONSTITUENTID',
        T.c.value('(AMOUNT)[1]','money') as 'AMOUNT',
        T.c.value('(SEQUENCE)[1]','integer') as 'SEQUENCE',
        T.c.value('(REVENUESOLICITORID)[1]','uniqueidentifier') as 'REVENUESOLICITORID',
        @APPLICATIONID as APPLICATIONID,
        @APPLICATIONCODE as APPLICATIONCODE,
        @DESIGNATIONID as DESIGNATIONID,
        @DECLINESGIFTAID,
      @SPONSORSHIPID
        from @SOLICITORS.nodes('/SOLICITORS/ITEM') T(c);

        fetch next from SOLICITORSCURSOR into @APPLICATIONCODE, @DESIGNATIONID, @APPLICATIONID, @SOLICITORS, @DECLINESGIFTAID, @SPONSORSHIPID
    end

    close SOLICITORSCURSOR    
    deallocate SOLICITORSCURSOR

    set @SOLICITORS =
    (
      select ID, CONSTITUENTID, AMOUNT, SEQUENCE, REVENUESOLICITORID, APPLICATIONCODE, DESIGNATIONID, APPLICATIONID, DECLINESGIFTAID, SPONSORSHIPID
      from @SOLICITOR
      for xml raw('ITEM'),type,elements,root('SOLICITORS'),binary base64
    );

    return @SOLICITORS
end