sql - 更新、查找/替换 xml 字段

Question

我的数据库中有一个页表。为简单起见，它有两列；标题和 xmlData它的标题类似于“我的例子”我有一个 xml 字段，如下所示:

<MA>
  <A>
    <URL>my-example-</URL>
    <id>5</id>
    </A>
</MA>

1. 我正在尝试查找替换任何末尾有“-”的 url，并删除最后一个尾随的“-”(如果存在)

2. 并删除尾随空格(如果有，到标题)

我可以抓取需要更改的行

select * from Pages
where title like '% '

(有一些连接和东西，但基本上就是这样)

Answer 1

这将为每行替换一次 URL。从您的示例 XML 来看，每行只有一个 URL。

with C1 as
(
  select xmlData,
         xmlData.value('(/MA/A/URL/text())[1]', 'nvarchar(500)') as URL
  from Pages
),
C2 as
(
  select xmlData,
         URL,
         left(URL, len(URL) - 1) as URL2
  from C1
  where right(URL, 1) = '-'
)
update C2 
set xmlData.modify('replace value of (/MA/A/URL/text())[1] 
                    with sql:column("C2.URL2")')

提取CTE C1中的URL值。
从 URL 中删除最后一个“-”并将其放入 CTE C2 的 URL2 中。同时删除不需要更新的行。
使用 modify() Method (xml Data Type) 更新 XML

这里是另一个版本，它在查询的 XML 部分完成这项工作。

update Pages
set xmlData.modify('replace value of (/MA/A/URL/text())[1] 
                    with fn:substring((/MA/A/URL/text())[1], 1, fn:string-length((/MA/A/URL/text())[1])-1)')
where xmlData.exist('/MA/A/URL[fn:substring(text()[1], fn:string-length(text()[1]), 1) = "-"]') = 1

一次只能更新一个节点，所以如果你在一行中有多个 URL，你必须将上面的代码放在一个循环中，只要有要更新的东西就进行更新。您可以使用 @@ROWCOUNT 检查更新是否更新了任何内容并重做更新直到 @@ROWCOUNT = 0。