How to delete node from XML column in DB

Question

I am trying to remove a particular "node" from an XML column in my SQL Table. Below is an example of one of the XML column contents.

<GodBrandConfig>
  <AppSecret>hello</AppSecret>
  <WebClientUrl>url</WebClientUrl>
  <AllowableIpAddresses>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>.*</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>178.160.245.88</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>178.160.245.88</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
  </AllowableIpAddresses>
  <GameplaySummaryUrl>about:blank</GameplaySummaryUrl>
</GodBrandConfig>

I am trying to delete duplicate records in here - For example " 178.160.245.88"

I have been trying many variations of the "delete" statement - Please can i have some assistance on this. set column.modify('delete /GodBrandConfig/AllowableIpAddresses/"178.160.245.88")') where idcolumn= 1125;

(1) Is 000.000.000.000 not considered as duplicate? It appears a few tens times. and (2) how do you deal with a value like .* ? — Ronen Ariely
– Ronen Ariely, Commented Sep 23, 2021 at 10:35
Ignore them - I was just "blanking" those IP's out. I have stripped the XML to only include the values i am trying to remove. — user3482471
– user3482471, Commented Sep 23, 2021 at 10:43

AlwaysLearning · Accepted Answer · 2021-09-23 11:15:07Z

1

You need to select an AllowableIpAddress node in your XPath, and since you're wanting to remove a duplicate you can delete the second occurrence that matches the specified text using something like:

update #DemoTable
set [column].modify('delete /GodBrandConfig/AllowableIpAddresses/AllowableIpAddress[text()="178.160.245.88"][2]')
where idcolumn = 1125;

Which yields the updated XML:

<GodBrandConfig>
    <AppSecret>hello</AppSecret>
    <WebClientUrl>url</WebClientUrl>
    <AllowableIpAddresses>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
        <AllowableIpAddress>.*</AllowableIpAddress>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
        <AllowableIpAddress>178.160.245.88</AllowableIpAddress>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
        <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    </AllowableIpAddresses>
    <GameplaySummaryUrl>about:blank</GameplaySummaryUrl>
</GodBrandConfig>

answered Sep 23, 2021 at 11:15

AlwaysLearning

8,9425 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ronen Ariely Over a year ago

(1) This clean only specific IP which you know in advance that is duplicated. (2) This only remove the second entity of the duplicated but what if you have more than two duplicated values?!? I see that this answer was selected as fit to the OP but I think that this is a mistake. Therefore, I will add another answer which cover all cases and remove nodes with duplicate values, for the sake of future readers :-)

Ronen Ariely Over a year ago

Note: if you want to remove all the nodes except the first one and not only the second one then you can use [position() > 1]

Ronen Ariely · Accepted Answer · 2021-09-23 15:29:33Z

Check if this fit your needs

DECLARE @myDoc XML
SET @myDoc = '<GodBrandConfig>
  <AppSecret>hello</AppSecret>
  <WebClientUrl>url</WebClientUrl>
  <AllowableIpAddresses>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>.*</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>178.160.245.88</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>178.160.245.88</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
    <AllowableIpAddress>000.000.000.000</AllowableIpAddress>
  </AllowableIpAddresses>
  <GameplaySummaryUrl>about:blank</GameplaySummaryUrl>
</GodBrandConfig>'  

-- Create clean node without duplication using "distinct-values"
DECLARE @AllowableIpAddress XML
SELECT @AllowableIpAddress = (
    SELECT @myDoc.query('
    <AllowableIpAddresses>
           {
                  for $x in distinct-values(//AllowableIpAddress/text())
                  return <AllowableIpAddress>{$x}</AllowableIpAddress>
           }
    </AllowableIpAddresses>
    ')
)

-- "replace node" is not supported in T-SQL (it is supported in oracle)
-- Therefore I will use modify with the actions "delete" and "insert"  
SET @myDoc.modify('delete (/GodBrandConfig/AllowableIpAddresses)')
SET @myDoc.modify('insert sql:variable("@AllowableIpAddress") after (/GodBrandConfig/WebClientUrl)[1]')
SELECT @myDoc ;

Notice that I removed all duplicated including the zero IP "000.000.000.000" as you said that this was added only for the sake of the question to fill the data and it is duplicated

Collectives™ on Stack Overflow

How to delete node from XML column in DB

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related