How to delete multiple nodes in XML using SQL

Question

I have a column name XMLData which contain large XML (around 10,000 lines). Below is the type of XML stored.

<ABC>
  <DEF>
    <GHI>
      <JKL>value1</JKL>
      ..
    </GHI>
    <GHI>
      <JKL>value2</JKL>
      ..
    </GHI>
    <GHI>
      <JKL>value3</JKL>
      ..
    </GHI>
    ..
    ..
    <GHI>
      <JKL>valueN</JKL>
      ..
    </GHI>
    <OtherNodes>
    <OtherNodes1>
    .
    .
    .
  </DEF>
</ABC>

Is there a way to delete multiple nodes in SQL query? Specifically, I want to delete all GHI nodes under the Node DEF. Thanks!!

Answer 1

For SQL Server, you'd just use modify with delete:

declare @x xml = '<ABC>
  <DEF>
    <GHI>
      <JKL>value1</JKL>
    </GHI>
    <GHI>
      <JKL>value2</JKL>
    </GHI>
    <GHI>
      <JKL>value3</JKL>
    </GHI>
    <GHI>
      <JKL>valueN</JKL>
    </GHI>
    <OtherNodes/>
    <OtherNodes1/>
  </DEF>
</ABC>'

set @x.modify('delete /ABC/DEF/GHI')

select @x

Result:

<ABC><DEF><OtherNodes /><OtherNodes1 /></DEF></ABC>