更新，查找/替换xml字段(Update, find/replace for xml field)

更新，查找/替换xml字段(Update, find/replace for xml field)

我的数据库中有一个Page表。 让我们说简单，它有两列; title和xmlData它的标题类似于“我的例子”，我有一个xml字段，如下所示：

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

我试图找到替换任何最后有“ - ”的网址，并删除唯一的最后一个尾随“ - ”如果它存在

并删除尾随空格（如果它有一个，到标题）

我能够通过执行来获取需要更改的行

select * from Pages where title like '% '

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

I have a Page table in my database. Lets say for simplicity it has two columns; title and xmlData It has Title something like "my example " and I have a xml field that looks like:

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

I am trying to do a find replace of any url that has "-" at the end, and remove the only the last trailing "-" if it exists

and remove the trailing space(if it has one, to title)

I am able to grab the rows that need to be changed by doing

select * from Pages where title like '% '

(there is some joins and stuff, but that is basically it)

最满意答案

这将替换每行的一次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中删除最后一个' - '并将其放在CT2 C2中的URL2中。 还要删除不需要更新的行。 使用modify（）方法更新XML （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 。

This will replace one occurrence of the URL for each row. By the look of your sample XML you have only one URL per row.

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")')

Extract the URL value in CTE C1. Remove the last '-' from the URL and put that in URL2 in CTE C2. Also remove the rows that does not need to be updated. Update the XML using modify() Method (xml Data Type)

And here is another version that does the job in the XML part of the query instead.

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

It is only possible to update one node at a time so if you have multiple URL's in in one row you have to put the code above in a loop and do the updates as long as there is something to update. You could use @@ROWCOUNT to check if the update did update anything and redo the update until @@ROWCOUNT = 0.