sql-server - 如何通过检查两个节点的值(包括需要进行包含(类似)比较的节点)来更新 sql server 中的 XML 列

Question

我在 Orders 表中有一个名为 OrderXML 的 xml 列...
表中有一个像这样的 XML XPath...

/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail

InternalOrderDetails 包含许多像这样的 InternalOrderDetail 节点...

<InternalOrderDetails>
  <InternalOrderDetail>
    <Item_Number>FBL11REFBK</Item_Number>
    <CountOfNumber>10</CountOfNumber>
    <PriceLevel>FREE</PriceLevel>
  </InternalOrderDetail>
  <InternalOrderDetail>
    <Item_Number>FCL13COTRGUID</Item_Number>
    <CountOfNumber>2</CountOfNumber>
    <PriceLevel>NONFREE</PriceLevel>
  </InternalOrderDetail>
</InternalOrderDetails>

我的最终目标是，如果节点的 Item_Number 包含 COTRGUID(如“%COTRGUID”)并且 PriceLevel=NONFREE，则修改 OrderXML 列中的 XML。如果满足该条件，我想将 PriceLevel 列更改为 FREE。

我在创建查找正确节点的 xpath 表达式(使用 OrderXML.value 或 OrderXML.exist 函数)和使用 OrderXML.modify 函数更新 XML 时遇到问题。

我已经为 where 子句尝试了以下操作:

WHERE OrderXML.value('(/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/Item_Number/node())[1]','nvarchar(64)') like '%13COTRGUID'

这确实有效，但在我看来，我还需要在同一个 where 子句中包含我的第二个条件 (PriceLevel=NONFREE)，但我不知道如何做到这一点。也许我可以为第二个条件添加 AND，如下所示...

AND OrderXML.value('(/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/PriceLevel/node())[1]','nvarchar(64)') = 'NONFREE'

但我担心它最终会像 OR 一样运行，因为它是一个 XML 查询。

一旦我得到正确的 WHERE 子句，我将使用如下所示的 SET 更新列:

UPDATE Orders SET orderXml.modify('replace value of (/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/PriceLevel[1]/text())[1] with "NONFREE"')

但是，我对一些测试数据运行了此语句，但没有更新任何 XML 列(即使它说 zz 行受到影响)。

我已经在这个问题上花了几个小时了，但毫无结果。感谢帮助。谢谢。

Answer 1

如果订单表的每一行中没有多个符合您的条件的节点，您可以使用以下命令:

update orders set
    data.modify('
        replace value of 
        (
            /Order/InternalInformation/InternalOrderBreakout/
            InternalOrderHeader/InternalOrderDetails/
            InternalOrderDetail[
                Item_Number[contains(., "COTRGUID")] and
                PriceLevel="NONFREE"
            ]/PriceLevel/text()
        )[1]
        with "FREE"
    ');

sql fiddle demo

如果一行中可以有多个节点，则有多种可能的解决方案，遗憾的是，每种解决方案都不是真正优雅。

• 您可以重建表中的所有 xml - sql fiddle demo
• 或者您可以在循环中进行更新 - sql fiddle demo