Delete node based on other node's attribute value

Question

I have the following XML being returned in SQL and I would like to use SQL XML DML to conditionally remove a node based on the value of a different node. In my example, I would like to remove the “Color” node when the value of the “Value” attribute of the “Size” node is equal to “S”.

<root>
            <ParentGroup>
                            <ChildGroup Id="1">
                                            <Child Id="1">
                                                            <Color Value="Red"/>
                                                            <Size Value="L"/>
                                            </Child>
                                            <Child Id="2">
                                                            <Color Value="Blue"/>
                                                            <Size Value="S"/>
                                            </Child>
                            </ChildGroup>
                            <ChildGroup Id="2">
                                            <Child Id="5">
                                                            <Color Value="Blue"/>
                                                            <Size Value="L"/>
                                            </Child>
                                            <Child Id="9">
                                                            <Color Value="Red"/>
                                                            <Size Value="S"/>
                                            </Child>
                            </ChildGroup>
            </ParentGroup>
</root>

I would like the resulting XML to be:

<root>
            <ParentGroup>
                            <ChildGroup Id="1">
                                            <Child Id="1">
                                                            <Color Value="Red"/>
                                                            <Size Value="L"/>
                                            </Child>
                                            <Child Id="2">
                                                            <Size Value="S"/>
                                            </Child>
                            </ChildGroup>
                            <ChildGroup Id="2">
                                            <Child Id="5">
                                                            <Color Value="Blue"/>
                                                            <Size Value="L"/>
                                            </Child>
                                            <Child Id="9">
                                                            <Size Value="S"/>
                                            </Child>
                            </ChildGroup>
            </ParentGroup>
</root>

Thanks in advance!

Answer 1

You can use a predicate to reduce the <Child>-nodes to a list, which fullfills your condition:

DECLARE @xml XML=
N'<root>
  <ParentGroup>
    <ChildGroup Id="1">
      <Child Id="1">
        <Color Value="Red" />
        <Size Value="L" />
      </Child>
      <Child Id="2">
        <Color Value="Blue" />
        <Size Value="S" />
      </Child>
    </ChildGroup>
    <ChildGroup Id="2">
      <Child Id="5">
        <Color Value="Blue" />
        <Size Value="L" />
      </Child>
      <Child Id="9">
        <Color Value="Red" />
        <Size Value="S" />
      </Child>
    </ChildGroup>
  </ParentGroup>
</root>';

--The query will search for a <Child> where the attribute Value within <Size> is "S" and remove their <Color>

SET @xml.modify('delete /root/ParentGroup/ChildGroup/Child[Size/@Value="S"]/Color');

SELECT @xml;

if you need to introduce the "S" as a variable you can use sql:variable()

DECLARE @SearchFor VARCHAR(10)='S';
SET @xml.modify('delete /root/ParentGroup/ChildGroup/Child[Size/@Value=sql:variable("@SearchFor")]/Color');