USP_TRANSACTIONALEMAIL_CHECKINVALIDACCOUNTS

      create procedure dbo.USP_TRANSACTIONALEMAIL_CHECKINVALIDACCOUNTS
      (
        @INVALIDJOBS xml output
      )
      as
      begin


        declare @EMAILADDRESSFIELD nvarchar(255);

        declare @INVALIDEMAILS table
        (
          ID nvarchar(36), -- we use this as a string because we have to do an string xquery later which is case sensitive and we need to be sure what is returned

          ADDRESS nvarchar(255)
        );

        -- using a cursor here due to the way we retrieve the data with xquery.  should never be that many loops anyway.    

        declare EMAILADDRESSCURSOR cursor local fast_forward for
          select distinct S.EMAILADDRESSFIELDNAME from dbo.EMAILSTATUSTRANSACTIONAL S
            inner join dbo.EMAILQUEUETRANSACTIONAL Q on Q.ID = S.EMAILQUEUETRANSACTIONALID;

        open EMAILADDRESSCURSOR;
        fetch next from EMAILADDRESSCURSOR into @EMAILADDRESSFIELD;

        while (@@FETCH_STATUS = 0)
          begin

            insert into @INVALIDEMAILS (ID, ADDRESS)
              select upper(cast(Q.ID as nvarchar(36))), recipientdata.value('(//*[local-name()=sql:variable("@EMAILADDRESSFIELD")])[1]','nvarchar(255)') [RECIPIENT] from EMAILQUEUETRANSACTIONAL Q
                inner join dbo.EMAILSTATUSTRANSACTIONAL S on Q.ID = S.EMAILQUEUETRANSACTIONALID
                where S.EMAILADDRESSFIELDNAME = @EMAILADDRESSFIELD;


            fetch next from EMAILADDRESSCURSOR into @EMAILADDRESSFIELD;
          end

        close EMAILADDRESSCURSOR;
        deallocate EMAILADDRESSCURSOR;

        set @INVALIDJOBS = (select I.ID, I.ADDRESS from @INVALIDEMAILS I inner join dbo.EMAILINVALIDRECIPIENT R on I.ADDRESS = R.ADDRESS and R.ISBLACKLISTED = 1
            for xml raw('JOB'),type,elements,root('EMAIL'),binary base64);

      end