您可以使用XQuery来修改XML。

create table sample (id, val) as
  select 1, '<Fields><TAG1>123</TAG1><TAG2>345</TAG2></Fields>' from dual
  union all
  select 2, '<Fields><TAG1>999</TAG1><TAG3>XXX</TAG3></Fields>' from dual

create table renaming(tag, renamed) as
  select 'TAG1','NEW_TAG1' from dual union all
  select 'TAG2','NEW_TAG2' from dual union all
  select 'TAG3','NEW_TAG3' from dual

select
  s.*
  , xmlserialize(document xmlquery(
    'copy $res := $x modify (
      for $r in $ren/ROWSET/ROW
      for $i in $res/Fields/*[name(.) = $r/TAG]
      return rename node $i as $r/RENAMED
    ) return $res'
    passing
      xmltype(s.val) as "x",
      /*Mapping XML*/
      dbms_xmlgen.getxmltype('select * from renaming') as "ren"
    returning content
  ) as clob) as renamed
from sample s

| ID|瓦尔|重新命名|
| - -----|- -----|- -----|
| 1|123345|<NEW_TAG1>123345</NEW_TAG1><NEW_TAG2></NEW_TAG2>|
| 2|小行星999|小<NEW_TAG1></NEW_TAG1><NEW_TAG3>行星999</NEW_TAG3>|
fiddle

UPD：如果要删除不匹配的标签或没有值的标签，可以使用多次修改。

select
  s.*
  , xmlserialize(document xmlquery(
    'copy $res := $x modify (
      (
        for $i in $res/Fields/*
        return (
          delete node $i[not($ren/ROWSET/ROW/TAG[text() = name($i)])],
          delete node $i[not(text())]
        )
      ),
      (
        for $renaming in $ren/ROWSET/ROW
        for $i in $res/Fields/*[name(.) = $renaming/TAG]
        return rename node $i as $renaming/RENAMED/text()
      )
    ) return $res'
    passing
      xmltype(s.val) as "x",
      /*Mapping XML*/
      dbms_xmlgen.getxmltype('select * from renaming') as "ren"
    returning content
  ) as clob) as renamed
from sample s

对于这个样本数据

create table sample (id, val) as
  select 1, '<Fields><TAG1>123</TAG1><TAG2>345</TAG2><TAG_WITH_NO_VALUE/></Fields>' from dual
  union all
  select 2, '<Fields><TAG1>999</TAG1><TAG3>XXX</TAG3></Fields>' from dual

create table renaming(tag, renamed) as
  select 'TAG_WITH_NO_VALUE','TAG_WITH_NO_VALUE__2' from dual union all
  select 'TAG2','NEW_TAG2' from dual union all
  select 'TAG3','NEW_TAG3' from dual

退货
| ID|瓦尔|重新命名|
| - -----|- -----|- -----|
| 1|123345<TAG_WITH_NO_VALUE/>|三百<NEW_TAG2>四十五</NEW_TAG2>|
| 2|小行星999|<NEW_TAG3>XXX</NEW_TAG3>|
fiddle

您可以通过生成XSLT来实现：

with data(xml) as (
    select xmltype(q'{<Fields><TAG1>123</TAG1><TAG2>345</TAG2></Fields>}') from dual union all
    select xmltype(q'{<Fields><TAG1>999</TAG1><TAG3>XXX</TAG3></Fields>}') from dual -- union all
),
mapping(oldtag, newtag) as (
    select 'TAG1', 'NEW_TAG1' from dual union all
    select 'TAG2', 'NEW_TAG2' from dual union all
    select 'TAG3', 'NEW_TAG3' from dual -- union all
),
xslt(xsl) as (
    select q'{<?xml version="1.0" encoding="UTF-8"?>
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:template match="/">
    <Fields>}' || listagg(tr,chr(10)) within group(order by rn)
    || '</Fields>' || q'{</xsl:template>
    </xsl:stylesheet>}' as xlst from (
        select rownum as rn, '<xsl:if test="/Fields/' || oldtag || '">' 
            || '<' || newtag || '><xsl:value-of select="/Fields/' || oldtag || '"/></' || newtag || '>' 
            || '</xsl:if>' 
            as tr
        from mapping
    )
)
select xmltransform(xml,xsl)
from data, xslt
;