SQL 更新查询以根据 xml 节点值更新记录中的 xml? [英] SQL Update query to update xml in records based on xml node value?

问题描述

我有一列 ntext 数据类型而不是 XML.它存储所有 xml 数据.我需要更新记录中的 xml 节点.它抛出一个错误，指出不正确使用 xml 数据类型方法‘修改’.在这种情况下需要一个非变异方法."

I have a column of ntext data type and NOT XML. It stores all xml data. I need to update xml node in the records. It throws an error saying "Incorrect use of the xml data type method 'modify'. A non-mutator method is expected in this context."

begin transaction
declare @Cps_Id int;
set @Cps_Id = 236;
declare @Cps_Message nvarchar(1024);
set @Cps_Message = 'updating cpsia message with smoking'; 

update table_name
set column_name =  CONVERT(xml,column_name).modify('replace value of (/root/ProductInformation/CPSIA/CpsiaDetails/Item[CpsiaId=sql:variable("@Cps_Id")]/CpsiaMessage/text())[1] with sql:variable("@Cps_Message")') 
WHERE Convert(xml,column_name).exist('/root/ProductInformation/CPSIA/CpsiaDetails/Item[CpsiaId=sql:variable("@Cps_Id")]')=1 

rollback  

示例 XML:

<root>
  <ProductInformation>
    <Name> Truck with Battery Charger</Name>
    <Description>Fr.</Description>
    <CPSIA>
      <CpsiaDetails>
        <Item>
          <CpsiaId>456</CpsiaId>
          <CpsiaMessage>waring</CpsiaMessage>
        </Item>
        <Item>
          <CpsiaId>236</CpsiaId>
          <CpsiaMessage>to health</CpsiaMessage>
        </Item>
      </CpsiaDetails>
    </CPSIA>
  </ProductInformation>
</root>

推荐答案

您需要对 XML 数据类型使用 modify 方法.

You need to use the modify method on the XML data type.

begin transaction
declare @Cps_Id int;
set @Cps_Id = 236;
declare @Cps_Message nvarchar(1024);
set @Cps_Message = 'updating cpsia message with smoking'; 

select id, CONVERT(xml,[text]) txt into #tmp from SO5954359

select * from #tmp

update #tmp
set txt.modify('replace value of (/root/ProductInformation/CPSIA/CpsiaDetails/Item[CpsiaId=sql:variable("@Cps_Id")]/CpsiaMessage/text())[1] with sql:variable("@Cps_Message")') 

select * from #tmp

drop table #tmp
rollback 

然后，您可以通过将更新后的临时表连接到键上的原始表来更新原始表.

You could then update the original table by joining the updated temporary table to the original table on the key.

这篇关于SQL 更新查询以根据 xml 节点值更新记录中的 xml?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！