1.实现的功能是把节点77替换成992.我的实现
declare @source varchar(2000),@xml xml
set @source='10,25,68,2,55,77,269'
set @xml=convert(xml,'<v>'+replace(@source,',','</v><v>')+'</v>')
declare @replaceStr varchar(20),@newStr varchar(20)
set @replaceStr = '77'
set @newStr='99'if @xml.exist('/v/text()=sql:variable("@replaceStr")') = 1
set @xml.modify('replace value of (/v[text()=sql:variable("@replaceStr")]) with sql:variable("@newStr")')select @xml
---执行时会发生如下错误,求大神如何修改
XQuery [modify()]: The target of 'replace' must be at most one node, found 'element(v,xdt:untyped) *'
declare @source varchar(2000),@xml xml
set @source='10,25,68,2,55,77,269'
set @xml=convert(xml,'<v>'+replace(@source,',','</v><v>')+'</v>')
declare @replaceStr varchar(20),@newStr varchar(20)
set @replaceStr = '77'
set @newStr='99'if @xml.exist('/v/text()=sql:variable("@replaceStr")') = 1
set @xml.modify('replace value of (/v[text()=sql:variable("@replaceStr")]) with sql:variable("@newStr")')select @xml
---执行时会发生如下错误,求大神如何修改
XQuery [modify()]: The target of 'replace' must be at most one node, found 'element(v,xdt:untyped) *'
改为
set @xml.modify('replace value of (/v[.=sql:variable("@replaceStr")]/text())[1] with sql:variable("@newStr")')这样就可以了~~~真是有点不明白为什么[.sql:variable("@replaceStr")]之后还要/text()
WHILE @xml.exist('(v[.= sql:variable("@replaceStr")])') = 1
begin
set @xml.modify('replace value of (/v[.= sql:variable("@replaceStr")]/text())[1] with sql:variable("@newStr")')
END
SELECT @xml
搞定了啊,为什么在加一个/text()
你看下下面的例子以及结合把text方法去掉之后的错误提示应该不难理解
DECLARE
@xml xml = N'
<a>aa
<b>bbb
<c>cc</c>
</b>
</a>';
SELECT
@xml.value('(/a)[1]', 'varchar(100)'),
@xml.value('(/a/text())[1]', 'varchar(100)')