xml中的update xml属性返回错误SQL(update xml attribute in xml returns error SQL)
我试图在SQL中更新xml的属性。
我的XML存储在变量@tmpRespXML中 ：
<Responses> <x id="3" name="Good" val="0" seq="0" createsr="0" /> <x id="4" name="Fair" val="0" seq="0" createsr="0" /> <x id="5" name="Needs Repair" val="1" seq="0" createsr="0" /> <x id="6" name="Not Inspected" val="1" seq="0" createsr="0" /> <x id="7" name="N/A" val="1" seq="0" createsr="0" /> </Responses>所以我做的是将xml放在临时表中。
DECLARE @tmpRespTBL TABLE(Responses XML) INSERT @tmpRespTBL VALUES(@tmpRespXML)然后更新表。 我正在尝试将属性@createsr设置为1，其中我的属性@id 等于 @items
UPDATE @tmpRespTBL SET Responses.modify('replace value of(/Responses/x[@id=("'+@items+'")]/@createsr)[1] with "1"')这会返回ff错误：
消息8172，级别16，状态1，行30 xml数据类型方法“modify”的参数1必须是字符串文字。
我在这里想念的是什么？
Im trying to update an attribute of an xml in SQL.
My XML is stored on a variable @tmpRespXML:
<Responses> <x id="3" name="Good" val="0" seq="0" createsr="0" /> <x id="4" name="Fair" val="0" seq="0" createsr="0" /> <x id="5" name="Needs Repair" val="1" seq="0" createsr="0" /> <x id="6" name="Not Inspected" val="1" seq="0" createsr="0" /> <x id="7" name="N/A" val="1" seq="0" createsr="0" /> </Responses>So what I did is to put the xml in a temp table.
DECLARE @tmpRespTBL TABLE(Responses XML) INSERT @tmpRespTBL VALUES(@tmpRespXML)and then update the table. I'm trying to set the attribute @createsr to 1 where my attribute @id is equal to @items
UPDATE @tmpRespTBL SET Responses.modify('replace value of(/Responses/x[@id=("'+@items+'")]/@createsr)[1] with "1"')This returns the ff error:
Msg 8172, Level 16, State 1, Line 30 The argument 1 of the xml data type method "modify" must be a string literal.
What am I missing here?
最满意答案
试试吧
SET Responses.modify('replace value of(/Responses/x[@id=("''+@items+''")]/@createsr)[1] with "1"')这应该可以解决你的问题。 我在这里做的是逃避'
Try with
SET Responses.modify('replace value of(/Responses/x[@id=("''+@items+''")]/@createsr)[1] with "1"')That should fix your issue. What I did here is escape the '
更多推荐
发布评论