在sql server中使用open xml执行存储过程时出错

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

我执行下面的存储过程。但它显示错误。错误是'语法不正确'。'。即错误显示在'xmlFields.Country'中也请查看此存储过程并提前帮助我，谢谢

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

2 个解决方案

#1

you can not do this @Country=xmlFields.Country

你不能这样做@ 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

你不能执行这样的程序，如果你想为每条记录执行程序使用sql游标来实现

#2

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.

1）使用游标进行逐行处理。

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

2）创建一个连接的EXEC语句列表到一个nvarchar（max）变量，然后可以用sp_executesql执行，或者，

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

3）理想情况下，使用存储过程中的逻辑，但是以基于集合的方式对照OPENXML SELECT的结果。

#1