sql查询以更新存储在列中的xml节点。

I am working on a project where I am using a table which have xml data stored in one of its column.I am trying to update my entire xml node based on xml_id and position of xml node but I am unable to do that.I tried the following query but its only updating the value of xml node not the entire key/value of node.

我正在开发一个项目，在这个项目中，我使用一个表，该表将xml数据存储在其中一个列中。我正在尝试基于xml_id和xml节点的位置更新整个xml节点，但是我不能这样做。我尝试了以下查询，但它只更新了xml节点的值，而没有更新节点的整个键/值。

Table structure

表结构

Query to update value of xml node

查询xml节点的更新值

update tblCCBT_Step_Page_Text_Xml
set Xml_XmlData.modify('replace value of (/page/*[position()=1]/text())[1] 
                with "test value"')where xml_id = 101

So now I tried the following query to update entire internal node

因此，现在我尝试了以下查询来更新整个内部节点

update tblCCBT_Step_Page_Text_Xml
set Xml_XmlData.modify('replace value of (/page/*[position()=1]/text())[1] 
                with "<testnode>test value</testnode>"') where xml_id = 101

But I am getting error while trying to do this

但是我在做这个的时候犯了错误

Msg 9306, Level 16, State 1, Line 2
XQuery [tblCCBT_Step_Page_Text_Xml.Xml_XmlData.modify()]: The target of 'replace value of' cannot be a union type, found '(element(*,xdt:untyped) | comment | processing-instruction | text) ?'.

Msg 9306，第16级，状态1，第2行XQuery [tblccbt_step_page_text_xml.xml_xml_xml_xmldata .modify()]: 'replace value of'的目标为' can ' t be a union type, found '(element(*，xdt:untyped) | comment | process - instructions | text) ?

This is my xml stored in the column

这是存储在列中的xml

<page name="page0" identifier="ff-102-101-101">
     <backBut>test value</backBut>
     <printBut>Print</printBut>
     <quiz1>Click on the circle that best describes your</quiz1>
     <quiz2>Continue</quiz2>
     <quiz3>Finish</quiz3>
     <quiz4>You are now on questions </quiz4>
     <quiz5>out of</quiz5>
     <quiz6>Please answer each question before continuing.</quiz6>
 </page>

Now in above xml I am trying to replace -

现在，在上面的xml中，我试图替换。

<backBut>test value</backBut> with <testnode>test value</testnode>

Please suggest how to achieve this. Thanks

请建议如何实现这一点。谢谢

2 个解决方案

#1

You could use an insert/delete approach.

您可以使用插入/删除方法。

Your code did not throw any error for me on SQL2008R2. But it did NOT give the desired result either.

您的代码在SQL2008R2上没有为我抛出任何错误。但它也没有给出预期的结果。

The statement resulted in replacing the text value of the node, not in replacing the node itself, as you can see below.

语句导致替换节点的文本值，而不是替换节点本身，如下所示。

<page name="page0" identifier="ff-102-101-101">
  <backBut>&lt;testnode&gt;test value&lt;/testnode&gt;</backBut>
  <printBut>Print</printBut>
  <quiz1>Click on the circle that best describes your</quiz1>
  <quiz2>Continue</quiz2>
  <quiz3>Finish</quiz3>
  <quiz4>You are now on questions </quiz4>
  <quiz5>out of</quiz5>
  <quiz6>Please answer each question before continuing.</quiz6>
</page>

You could achieve it via insert/delete:

可通过插入/删除实现:

First insert the new node:

首先插入新节点:

UPDATE tblCCBT_Step_Page_Text_Xml
SET Xml_XmlData.modify('insert <testnode>test value</testnode> into /page[1]')
WHERE xml_id = 101

Then simply delete the old node:

然后简单地删除旧的节点:

UPDATE tblCCBT_Step_Page_Text_Xml
SET Xml_XmlData.modify('delete (/page/backBut)[1]')
WHERE xml_id =101

#2

Can't believe I did it :)

真不敢相信我做到了

declare @t table (
    Id int,
    XMLData xml
);

insert into @t (Id, XMLData)
values
    (101, N'<page name="page0" identifier="ff-102-101-101">
     <backBut>test value</backBut>
     <printBut>Print</printBut>
     <quiz1>Click on the circle that best describes your</quiz1>
     <quiz2>Continue</quiz2>
     <quiz3>Finish</quiz3>
     <quiz4>You are now on questions </quiz4>
     <quiz5>out of</quiz5>
     <quiz6>Please answer each question before continuing.</quiz6>
 </page>');

-- Before modification
select * from @t;

update t set XMLData = nt.ModXML
from @t t
    inner join (
        select t2.Id, t2.XMLData.query('
element page {
    /page/@*,
    for $n in /page/*
    return
        if (local-name($n) = "backBut") then
            <testnode>test value</testnode>
        else
            $n
}') as [ModXML]
        from @t t2
    ) nt on t.Id = nt.Id
where t.Id = 101;

-- After modification
select * from @t;

As you can see, it's not an XML update, strictly speaking - I am replacing the contents of the entire XML field. Also, it might stop working in case of more complex structure. And, well, performance can be an issue, too.

正如您所看到的，严格地说，这不是XML更新——我正在替换整个XML字段的内容。此外，如果结构更复杂，它可能会停止工作。性能也是一个问题。