error come to execute the stored procedure using with open xml in sql server

Question

Hi, i execute the below stored procedure.but it shows the error. The error is 'Incorrect syntax near '.'.i.e error shows in 'xmlFields.Country' please look this stored procedure also and help me thanks, in advance

create procedure sp_SuUpdateSUADUsersStatus
(
    @FinalEMPCode nvarchar(50),
    @xmlFields NTEXT 
)
AS  
DECLARE @CityIDReturn INT
SET NOCOUNT ON  
BEGIN  
 DECLARE @hdoc INT  

 EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmlFields

          BEGIN
         EXEC @CityIDReturn=sp_SuSaveADUsersLocation @Country=xmlFields.Country,
          xmlFields.State,xmlFields.City
         FROM OPENXML(@hDoc, 'EmpCode/User', 2)   WITH 
         (Country nvarchar(500),State nvarchar(500),City nvarchar(500))
         as xmlFields
         where xmlFields.Country <>'' and xmlFields.State <>'' and xmlFields.City   
                 <>'')
      END



    EXEC sp_xml_removedocument @hdoc


End

Answer 1

It looks like you are trying to execute a stored procedure for each row of the result set. You can't do it like that. You would need to either

1) use a cursor for row by row processing.

2) create a concatenated list of EXEC statements into a nvarchar(max) variable that can then be executed with sp_executesql, or,

3) ideally, use the logic from the stored procedure but do it in a set based way against the results of the OPENXML SELECT.

Answer 2

you can not do this @Country=xmlFields.Country

try below code

  EXEC @CityIDReturn=sp_SuSaveADUsersLocation xmlFields.Country,
          xmlFields.State,xmlFields.City

you cannot execute procedure like this if you want to execute procedure for each record use sql cursor to achieve