--简单地,可以这样写:create proc p_update @xpath char(50), @value char(50) as declare @s Nvarchar(4000) select @s=case len(@xpath)-len(replace(@xpath,'\','')) when 3 then 'ship' when 4 then 'address' else '' end if @s='' begin set @s=rtrim(@xpath) raiserror('无效的path"%s"',1,16,@s) return end set @s='update '+@s+' set ' +rtrim(right(@xpath,charindex('\',reverse(@xpath))-1)) +'=@value' --print @s exec sp_executesql @s,N'@value char(50)',@value go--调用 exec p_update '\invoice\ship\name','aa'
根据这个xpath比如:"\invoice\ship\name"来update 数据库更新为什么? 更新为value传递的值?
Id char(40) key
@xpath char(50),
@value char(50)
as
declare @s Nvarchar(4000)
select @s=case len(@xpath)-len(replace(@xpath,'\',''))
when 3 then 'ship'
when 4 then 'address'
else '' end
if @s=''
begin
set @s=rtrim(@xpath)
raiserror('无效的path"%s"',1,16,@s)
return
end
set @s='update '+@s+' set '
+rtrim(right(@xpath,charindex('\',reverse(@xpath))-1))
+'=@value'
--print @s
exec sp_executesql @s,N'@value char(50)',@value
go--调用
exec p_update '\invoice\ship\name','aa'
对应invoice表中的id
我的xpath希望是这样的格式:\invoice[@Id='12']\ship\name'
应该怎么改呀?谢谢
set @xpath='\invoice[@Id=''13'']\ship\name' if patindex('%[[@]%['']%['']]%',@xpath)>0
select @value=stuff(@xpath,1,patindex('%[[@]%['']%['']]%',@xpath),'')
,@value=stuff(@value,1,charindex('''',@value),'')
,@value=left(@value,charindex(''']',@value)-1)
select @value