如何通过SQL根据子节点值删除XML中的节点及其子节点

可以通过SQL实现，下面以SQL Server为例（不同数据库处理XML的语法略有差异，这里讲解最常用的场景）：

假设你的XML数据存储在一张表的XML类型字段中，比如表名为DeliveryDocs，字段名为DeliveryXML，XML结构大致如下：

<root>
  <ReceiveDeliveryItem>
    <DocumentID>
      <ID>13600000493</ID>
    </DocumentID>
    <OrderRef>
      <LineNumber>1</LineNumber>
    </OrderRef>
    <!-- 其他子节点 -->
  </ReceiveDeliveryItem>
  <ReceiveDeliveryItem>
    <!-- 其他符合/不符合条件的节点 -->
  </ReceiveDeliveryItem>
</root>

实现步骤 #

使用SQL Server的XML.modify()方法结合XPath定位，删除满足条件的ReceiveDeliveryItem节点及其所有子节点：

-- 更新表，删除指定条件的节点
UPDATE DeliveryDocs
SET DeliveryXML.modify('
    delete /root/ReceiveDeliveryItem[DocumentID/ID = "13600000493" and OrderRef/LineNumber = "1"]
')
-- 只更新存在目标节点的行，避免无效操作
WHERE DeliveryXML.exist('
    /root/ReceiveDeliveryItem[DocumentID/ID = "13600000493" and OrderRef/LineNumber = "1"]
') = 1;

关键说明 #

• modify()是SQL Server XML类型专属的修改方法，支持delete/insert/replace三种操作；
• XPath表达式中，[DocumentID/ID = "xxx" and OrderRef/LineNumber = "xxx"]用于过滤出同时满足两个子节点条件的ReceiveDeliveryItem节点；
• exist()方法用于判断行中是否存在符合条件的节点，避免对无目标节点的行执行更新；
• 如果你的XML根节点不是<root>，需要将XPath中的/root替换为实际的根节点路径（比如/ReceiveDeliveryItems）。

其他数据库思路（以PostgreSQL为例） #

如果用PostgreSQL，可通过xpath函数定位节点，再结合xmlforest等函数重构XML来实现删除：

UPDATE DeliveryDocs
SET DeliveryXML = (
  SELECT xmlagg(xmlforest(
    (xpath('./node()', item))[1] AS "ReceiveDeliveryItem"
  ))
  FROM unnest(xpath('/root/ReceiveDeliveryItem', DeliveryXML)) AS item
  WHERE (xpath('./DocumentID/ID/text()', item))[1]::text != '13600000493'
     OR (xpath('./OrderRef/LineNumber/text()', item))[1]::text != '1'
)
WHERE EXISTS (
  SELECT 1
  FROM unnest(xpath('/root/ReceiveDeliveryItem', DeliveryXML)) AS item
  WHERE (xpath('./DocumentID/ID/text()', item))[1]::text = '13600000493'
    AND (xpath('./OrderRef/LineNumber/text()', item))[1]::text = '1'
);

内容的提问来源于stack exchange，提问作者LJHHouston