时间戳

首页 > 解决方案 > 如何从 XML 节点而不是 SQL 表中的完整节点中删除特定值

sql-server - 如何从 XML 节点而不是 SQL 表中的完整节点中删除特定值

问题描述

我的表中有一个 xml 字段：具有各种标签的 XmlDoc.. 但我需要从标签中删除特定值，而不是删除整个标签本身。

我已经尝试过使用

update table
set XmlDoc.modify('delete //DeliveryMechanism//Address//text()[contains("abc@gmail.com")]')

但它会删除标签中的所有值，但我只需要删除 abc@gmail.com”

<DeliveryMechanism>
    <ID>1</ID>
    <Name>Email</Name>
    <Description />
    <IsActive>false</IsActive>
    <Address>def@gmail.com,abc@gmail.com,hij@gmail.com</Address>
    <DeliveryOptions xmlns:p3="http://www.w3.org/2001/XMLSchema-instance"      p3:type="DeliveryOptionsEmail">
        <MailPriority>Normal</MailPriority>
    </DeliveryOptions>
</DeliveryMechanism>

我需要标签就像

<Address>def@gmail.com,hij@gmail.com</Address>

标签： sql-serverxmltsqlxpathxquery

解决方案

这并不像你想象的那么简单……

问题是：您永远不应该将数据存储为分隔字符串。与其使用逗号分隔的电子邮件地址列表，不如使用嵌套节点列表。但有时我们不得不坚持糟糕的设计。这是解决此问题的一种方法：

具有三个测试用例的模型场景：

DECLARE @mockTable TABLE(ID INT IDENTITY, Descr VARCHAR(100), yourXml XML);
INSERT INTO @mockTable VALUES
('Your sample'
,'<DeliveryMechanism>
    <ID>1</ID>
    <Name>Email</Name>
    <Description />
    <IsActive>false</IsActive>
    <Address>def@gmail.com,abc@gmail.com,hij@gmail.com</Address>
    <DeliveryOptions xmlns:p3="http://www.w3.org/2001/XMLSchema-instance"      p3:type="DeliveryOptionsEmail">
        <MailPriority>Normal</MailPriority>
    </DeliveryOptions>
</DeliveryMechanism>') 
,('Your sample without the given mail address'
,'<DeliveryMechanism>
    <ID>1</ID>
    <Name>Email</Name>
    <Description />
    <IsActive>false</IsActive>
    <Address>def@gmail.com,hij@gmail.com;oneMore@test.com</Address>
    <DeliveryOptions xmlns:p3="http://www.w3.org/2001/XMLSchema-instance"      p3:type="DeliveryOptionsEmail">
        <MailPriority>Normal</MailPriority>
    </DeliveryOptions>
</DeliveryMechanism>') 
,('Your sample with twice the given mail address'
,'<DeliveryMechanism>
    <ID>1</ID>
    <Name>Email</Name>
    <Description />
    <IsActive>false</IsActive>
    <Address>abc@gmail.com,hij@gmail.com,abc@gmail.com</Address>
    <DeliveryOptions xmlns:p3="http://www.w3.org/2001/XMLSchema-instance"      p3:type="DeliveryOptionsEmail">
        <MailPriority>Normal</MailPriority>
    </DeliveryOptions>
</DeliveryMechanism>');

--一个变量来设置我们要查找和删除的电子邮件

DECLARE @givenAddress VARCHAR(100)='abc@gmail.com';

--查询

WITH PreComputation AS
(
    SELECT t.*
          ,STUFF(
           CAST('<x>' + REPLACE(t.yourXml.value('(/DeliveryMechanism/Address/text())[1]','nvarchar(max)'),',','</x><x>') + '</x>' AS XML)
                .query('for $a in /x[text()[1] != sql:variable("@givenAddress")]/text()
                        return
                        <x>{concat(",",$a)}</x>').value('.','nvarchar(max)'),1,1,'') theNewList
    FROM @mockTable t
    WHERE t.yourXml.exist('/DeliveryMechanism[Address[contains(text()[1],sql:variable("@givenAddress"))]]')=1
) 
UPDATE PreComputation SET yourXml.modify('replace value of (/DeliveryMechanism/Address/text())[1] with sql:column("theNewList")');

--查看结果

SELECT * FROM @mockTable;

简而言之：

CTEPreComputation将首先用于.exist()将工作减少到包含特定地址的行上。
下一步是使用一些字符串操作，将您的 CSV 列表转换为 XML 并将其转换为原生 XML。
一旦有了原生 XML，我们就可以使用XQuery
The.query()将返回所有电子邮件片段，其中text()不等于给定地址。
一些技巧concat()，.value()后来STUFF()，我们现在有了没有给定地址的新创建的 CSV 列表。
最后UPDATE可以用这个和sql:column().

祝你好运 ;-）

推荐阅读