CODEWITHSUNDEEP
sql-serverxmlt-sqlxpathxquery
Myachizero
Myachizero

Reputation: 47

TSQL Need to delete an XML node based on a child's value with conditions

I got an XML like this:

<root>
     <child1>
             <...../>
             <...../>
             <...../>
             <child2>
                     <child3>Value1</child3>
                     <child3>Value2</child3>
                     <child3>Value3</child3>
             </child2>
     </child1>
</root>

I've been searching for a few days but can't quite find an appropriate answer to this problem I've encountered.

Essentially, I want to delete the node child1 from the root and all its children based on a value (In this case, Value1) from child3, but there may be multiple child3 nodes and I want to preserve child3 if it has values I want (In this case, it could be Value2 or Value3), even if it has a value (Value1) I'm trying to delete.

Currently, this is what I am using.

set @xml.modify('delete root/child1[child2[child3 = "Value1"]]')

What I want to do is

set @xml.modify('delete root/child1[child2[child3 = "Value1"] AND !(child2[child3 = "Value2"] OR child2[child3 = "Value3"])]')

So essentially, Don't delete nodes that have the data in the child that I want, even if it has a specific value I do not want, and then delete the nodes I don't want in every other case.

There are other ways I can do this, by querying an ID from the root node and doing a ton of prework, but I'd like to make the code as consolidated as possible or otherwise have more intelligent code than that. Any thoughts?

Upvotes: 2

Views: 1492

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

I do not know, if I really got this correctly, but you might solve this with a FLWOR-query()-cascade:

DECLARE @xml xml = 
N'<root>
    <child1 id="1" type="Is to be deleted completely">
        <child2>
            <child3>Value1</child3>
        </child2>
        <child2>
            <child3>Value1</child3>
        </child2>
    </child1>

    <child1 id="2" type="Should remain but those with the search value should be deleted">
        <child2>
            <child3>Value1</child3>
            <child3>Other a</child3>
            <child3>Other c</child3>
        </child2>
    </child1>

    <child1 id="3" type="First child2 delete, second just delete the one with delete value, third untouched">
        <child2>
            <child3>Value1</child3>
        </child2>
        <child2>
            <child3>Value1</child3>
            <child3>Other a</child3>
            <child3>Other c</child3>
        </child2>
        <child2>
            <child3>Other a</child3>
            <child3>Other c</child3>
        </child2>
    </child1>

    <child1 id="4" type="Untouched, no search value">
        <child2>
            <child3>Other m</child3>
        </child2>
        <child2>
            <child3>Other n</child3>
        </child2>
    </child1>
</root>';

DECLARE @DeleteValue NVARCHAR(100)='Value1';

  • the first FLWOR-query() will rebuild the XML without the nodes carrying the search value

  • the second FLWOR-query() will remove all child2 elements with no content

  • the third FLWOR-query() will remove all child1 elements with no content

This is the query:

SELECT @xml.query
(
N'
<root>
    {
    for $c1 in /root/child1
    return
        <child1> {$c1/@*}
        {
        for $c2 in $c1/child2
        return
            <child2> {$c2/@*}
            {
            for $c3 in $c2/child3[text()!=sql:variable("@DeleteValue")]
            return $c3 
            }
            </child2>
        }
        </child1>
    }
</root>'
).query 
(
N'
<root>
{
    for $c1 in /root/child1
    return
        <child1> {$c1/@*}
        {
        for $c2 in $c1/child2[string-length(.)!=0]
        return $c2 
        }
        </child1>
}
</root>'
).query
(
N'
<root>
{
    for $c1 in /root/child1[string-length(.)!=0]
    return $c1 
}
</root>'
);

This is the result

<root>
  <child1 id="2" type="Should remain but those with the search value should be deleted">
    <child2>
      <child3>Other a</child3>
      <child3>Other c</child3>
    </child2>
  </child1>
  <child1 id="3" type="First child2 delete, second just delete the one with delete value, third untouched">
    <child2>
      <child3>Other a</child3>
      <child3>Other c</child3>
    </child2>
    <child2>
      <child3>Other a</child3>
      <child3>Other c</child3>
    </child2>
  </child1>
  <child1 id="4" type="Untouched, no search value">
    <child2>
      <child3>Other m</child3>
    </child2>
    <child2>
      <child3>Other n</child3>
    </child2>
  </child1>
</root>

Upvotes: 0

gofr1
gofr1

Reputation: 15997

You can use XML.modify() method with contains function (not(contains..)):

DECLARE @x xml = N'<root>
<child1>
    <child2>
        <child3>Value1</child3>
        <child3>Value2</child3>
        <child3>Value3</child3>
    </child2>
</child1>
<child1>
    <child2>
        <child3>Value1</child3>
        <child3>Value4</child3>
        <child3>Value5</child3>
    </child2>
</child1>
<child1>
    <child2>
        <child3>Value5</child3>
        <child3>Value2</child3>
        <child3>Value6</child3>
    </child2>
</child1>
</root>'


SET @x.modify('delete /root/child1[contains(.,"Value1") and (not(contains(.,"Value2")) or not(contains(.,"Value3")))]')

SELECT @x

Output will be:

<root>
  <child1>
    <child2>
      <child3>Value1</child3>
      <child3>Value2</child3>
      <child3>Value3</child3>
    </child2>
  </child1>
  <child1>
    <child2>
      <child3>Value5</child3>
      <child3>Value2</child3>
      <child3>Value6</child3>
    </child2>
  </child1>
</root>

Upvotes: 3

Related Questions