在 SQL Server 中更新空 XML 标记 [英] Update Empty XML Tag in SQL Server

问题描述

我正在尝试在 sql server 上更新我的 Xml 字符串中的空 XML 标记；它说当我运行以下脚本时该行已更新，但是当我查看 XML 时；什么都没有改变:

I'm trying to update an empty XML Tag in my Xml string on sql server; it says the row is updated when i run the following script, but when I view the XML; nothing has changed:

Declare @newValue varchar(100)
select @newValue = '01'

    update dbo.UploadReport
    set XmlTest.insert('replace value of (/CodeFiveReport/Owner/AgencyID/text())[1] with sql:variable("@newValue")') 
    where id = 'myId'

后面的xml在数据库中还是这样显示

The xml after still appears as this in the databse

我做错了什么?

我已经试过@AgencyID 最后没有 text() ，但仍然无济于事......

I've tried @AgencyID without the text() at the end and still no avail...

推荐答案

据我自己的经验，不能一步完成，因为元素确实没有 text() - 因此，您无法替换它.

As far as I know from my own experience, you cannot do this in one step, since the <AgencyID/> element really has no text() - so therefore, you cannot replace it.

您可能必须使用以下内容:

You might have to use something like:

DECLARE @newValue VARCHAR(100)
SELECT @newValue = '01'

-- first update - add a new <AgencyID>...</AgencyID> node    
UPDATE dbo.UploadReport
SET XmlTest.modify('insert <AgencyID>{sql:variable("@newValue")}</AgencyID> as last into (/CodeFiveReport/Owner)[1]') 
WHERE id = 'myId'

-- second update - remove the empty <AgencyID /> node    
UPDATE dbo.UploadReport
SET XmlTest.modify('delete (/CodeFiveReport/Owner/AgencyID)[1]') 
WHERE id = 'myId'

一件事:您需要使用 XmlTest.modify 操作 - 而不是您在帖子中使用的 XmlTest.insert.SQL Server XML 列上没有 .insert() 函数.

One thing: you need to use the XmlTest.modify operation - not XmlTest.insert as you have in your post. There is no .insert() function on SQL Server XML columns.

这篇关于在 SQL Server 中更新空 XML 标记的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！