question

have update the script with the following but we still have a problem with an infinite loop

--Create Temp table
create table #TempIFSC
(
    [EnrolledPaymentMethodAccountId] uniqueidentifier, 
    [PaymentAccountId] uniqueidentifier, 
    [ExternalSystemId] int, 
    [EnrolledPaymentMethodAccountStatusId] int, 
    [Extension] xml,
    [EnrollmentAccountRevisionId] int,
[BankName] NVARCHAR(100) NULL,
[BankBranch] NVARCHAR(100) NULL,
[IFSC] NVARCHAR(100) NULL,
[IsUpdate] bit NULL
)

--Insert record into temp table who does not have IFSC code and currency INR
Insert Into #TempIFSC 
Select EP.[EnrolledPaymentMethodAccountId],EP.[PaymentAccountId],EP.[ExternalSystemId],EP.[EnrolledPaymentMethodAccountStatusId],CAST(EP.[Extension] AS XML),EP.[EnrollmentAccountRevisionId],NULL,NULL,NULL,0
from [dbo].[EnrolledPaymentMethodAccount] EP With (NOLOCK)
INNER JOIN [dbo].[PaymentAccount] PA With (NOLOCK) ON EP.PaymentAccountId = PA.PaymentAccountId and PA.CurrencyCode = 'INR'
where ISNULL(CAST(EP.Extension AS XML).value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'),'') = '' 
and EP.ExternalSystemId = 52 
and EP.EnrolledPaymentMethodAccountStatusId = 1 

--Update the BankName and BankBranch value in temp table
Declare @Rowcount INT = 1;
WHILE (@Rowcount > 0)  
Begin
UPDATE TOP (4999) #TempIFSC
SET
BankName = b.Name,
BankBranch = TMP.Extension.value('(//*[local-name()="Value"])[3]', 'NVARCHAR(255)'),
IFSC = IM.IFSC,
IsUpdate = 1
FROM #TempIFSC TMP
INNER JOIN [dbo].[Bank] b WITH(NOLOCK) ON b.ExternalSystembankId = TMP.Extension.value('(//*[local-name()="Value"])[2]', 'NVARCHAR(255)') AND b.ExternalSystemId = TMP.ExternalSystemId 
INNER JOIN [taurus].[IFSCMasterList] IM WITH(NOLOCK) ON b.Name = IM.BankName AND TMP.Extension.value('(//*[local-name()="Value"])[3]', 'NVARCHAR(255)') = IM.BankBranchName
Where b.CurrencyCode = 'INR'
AND b.Name IS NOT NULL
AND ISNULL(TMP.Extension.value('(//*[local-name()="Value"])[3]', 'NVARCHAR(255)'),'') != ''

SET @Rowcount = @@ROWCOUNT;

print @Rowcount

RETURN 
End

--Remove the records which does not have BankName or BankBranch
DELETE FROM #TempIFSC WHERE BankName IS NULL OR BankBranch IS NULL OR IFSC IS NULL

--Update IFSC value in temp table
--Declare @IFSCRowcount INT = 1;
--WHILE (@IFSCRowcount > 0)   
--BEGIN
--UPDATE TOP (4999) #TempIFSC
--SET IFSC = IM.IFSC
--FROM #TempIFSC TMP
--INNER JOIN [taurus].[IFSCMasterList] IM WITH(NOLOCK) 
--ON TMP.BankBranch = IM.BankBranchName and TMP.BankName = IM.BankName
--WHERE IM.BankName IS NOT NULL 
--AND IM.IFSC IS NOT NULL
--AND IM.BankBranchName IS NOT NULL
----AND TMP.Extension.exist(N'/*/NameValueEntity/Name[text()="IFSCCode"]') = 1
----OR ISNULL(TMP.Extension.value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'),'') = ''

--SET @IFSCRowcount = @@ROWCOUNT;

--CHECKPOINT;   --<-- to commit the changes with each batch
--End


--Remove blank node of IFSC
Declare @XMLRowcount INT = 1;

WHILE (@XMLRowcount > 0)   
BEGIN
DECLARE @NodeName NVARCHAR(500) = 'NameValueEntity'
Update TOP (4999) #TempIFSC
SET Extension.modify('delete /ArrayOfNameValueEntity/*[local-name(.) eq sql:variable("@NodeName")][6]') 
FROM #TempIFSC
Where Extension.exist(N'/*/NameValueEntity/Name[text()="IFSCCode"]') = 1 and ISNULL(Extension.value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'),'') = ''

SET @XMLRowcount = @@ROWCOUNT;
Print @XMLRowcount
CHECKPOINT;   --<-- to commit the changes with each batch
END

--Update extension value in temp table
Declare @CodeRowcount INT = 1;

WHILE (@CodeRowcount > 0)   
BEGIN
Update TOP (4999) #TempIFSC
SET Extension.modify('insert <NameValueEntity><Name>IFSCCode</Name><Value>{sql:column("#TempIFSC.IFSC")}</Value></NameValueEntity>
into (/ArrayOfNameValueEntity)[1]')
--FROM #TempIFSC
WHERE ISNULL(IFSC,'') <> ''
AND BankName IS NOT NULL
AND IsUpdate = 1
AND ISNULL(Extension.value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'),'') = ''

SET @CodeRowcount = @@ROWCOUNT;

CHECKPOINT;   --<-- to commit the changes with each batch
END

--Actual table updated from ##TempIFSC
Declare @TblRowcount INT = 1;

--WHILE (@TblRowcount > 0)   
--BEGIN
--Update TOP (4999) [dbo].[EnrolledPaymentMethodAccount_20220921]
--SET [Extension] = CAST(TMP.[Extension] AS nvarchar(max))
--FROM [dbo].[EnrolledPaymentMethodAccount_20220921] EP WITH(NOLOCK)
--INNER JOIN #TempIFSC TMP WITH(NOLOCK) ON EP.[PaymentAccountId] = TMP.PaymentAccountId AND EP.[EnrolledPaymentMethodAccountId] = TMP.[EnrolledPaymentMethodAccountId]
--Where EP.[ExternalSystemId] = TMP.ExternalSystemId
--AND EP.[EnrolledPaymentMethodAccountStatusId] = TMP.EnrolledPaymentMethodAccountStatusId
--AND EP.[EnrollmentAccountRevisionId] = TMP.EnrollmentAccountRevisionId
--AND TMP.IsUpdate = 1
--AND TMP.IFSC <> '' OR TMP.IFSC IS NULL
--AND TMP.BankName IS NOT NULL AND TMP.BankBranch IS NOT NULL
--AND ISNULL(TMP.Extension.value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'),'') != ''

--SET @TblRowcount = @@ROWCOUNT;

--CHECKPOINT;   --<-- to commit the changes with each batch
--END

Select * from #TempIFSC WITH(NOLOCK)

DROP Table #TempIFSC

This is the part that is still looping indefinitely

figured out that it was syntax error

AND TMP.IFSC <> '' OR TMP.IFSC IS NULL