英文:
How do I update an XML column in SQL Server using modify?
问题
我有一个SQL Server表的XML数据类型列中的以下值：
<feed>
<schedule>
<arrivalSla dayOfWeek="monday" addDays="1" time="02:00:00"/>
<arrivalSla dayOfWeek="tuesday" addDays="1" time="02:00:00"/>
<arrivalSla dayOfWeek="wednesday" addDays="1" time="02:00:00"/>
<arrivalSla dayOfWeek="thursday" addDays="1" time="02:00:00"/>
<arrivalSla dayOfWeek="friday" addDays="3" time="12:45:00"/>
</schedule>
<assetCode value="FI" />
</feed>
我想要将dayOfWeek="friday"的时间属性更新为"11:00:00"。我尝试了各种方法，包括以下方法，但出现以下错误：
XQuery [learnTheXML.xnlColumn.modify()]: The target of 'replace' must be at most one node, found 'element(arrivalSla,xdt:untyped) *'
UPDATE learnTheXML
SET xmlColumn.modify('replace value of (/feed/schedule/arrivalSla[@dayOfWeek="friday"]) with "11:00:00"')
WHERE id = 1
我做错了什么？
英文:
I have the following value in an XML data type column of a SQL Server table:
<feed>
<schedule>
<arrivalSla dayOfWeek="monday" addDays="1" time="02:00:00"/>
<arrivalSla dayOfWeek="tuesday" addDays="1" time="02:00:00"/>
<arrivalSla dayOfWeek="wednesday" addDays="1" time="02:00:00"/>
<arrivalSla dayOfWeek="thursday" addDays="1" time="02:00:00"/>
<arrivalSla dayOfWeek="friday" addDays="3" time="12:45:00"/>
</schedule>
<assetCode value="FI" />
</feed>
I want to update the time attribute where dayOfWeek="friday" to "11:00:00". I have tried various ways, including the following, but get an error of
> XQuery [learnTheXML.xnlColumn.modify()]: The target of 'replace' must be at most one node, found 'element(arrivalSla,xdt:untyped) *'
UPDATE learnTheXML
SET xmlColumn.modify('replace value of (/feed/schedule/arrivalSla[@dayOfWeek="friday"]) with "11:00:00"')
WHERE id = 1
What am I doing wrong?
答案1
得分: 1
你仍然需要提供一个单一数值来更新，并且你需要告诉它你想要更新（大概是）time的值：
UPDATE learnTheXML
SET xmlColumn.modify('replace value of (/feed/schedule/arrivalSla[@dayOfWeek="friday"]/@time)[1] with "11:00:00"')
WHERE id = 1;
英文:
You still need to provide a singleton value to update and also you need to tell it you want to update (presumably) the value of time:
UPDATE learnTheXML
SET xmlColumn.modify('replace value of (/feed/schedule/arrivalSla[@dayOfWeek="friday"]/@time)[1] with "11:00:00"')
WHERE id = 1;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论