How do I update an XML column in SQL Server using modify?

jq6vz3qz 于 2023-02-18 发布在 SQL Server

关注(0)|答案(1)|浏览(173)

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?

sql-server

来源：https://stackoverflow.com/questions/75487694/how-do-i-update-an-xml-column-in-sql-server-using-modify

1条答案

按热度按时间

ssm49v7z1#

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;

赞(0）回复(0）举报 2023-02-18

我来回答

How do I update an XML column in SQL Server using modify?

1条答案

相关问题

热门标签

最新问答