Oracle query to rename tags inside XMLTYPE

I have a table (Table1) with an XMLType field (PROPERTIES) and I have another table (Table2) with 2 fields:

• TAG_NAME
• TAG_RENAME

Each XMLType record may have different tag names.
I need to get as output of select, the XMLType field with tags renamed (I don't need to update field).

Here an example:

Table1:

Table2:

Output needed:

I tried with XQuery in this way (left join because some PROPERTIES fields can be NULL):

select t.ID, t.tag_name, t.value FROM Table1 t
left join  XMLTable ('for $i in fn:collection("oradb:/MY_USER/TABLE2"), $x in //descendant::*
where $i/ROW/TAG_NAME=local-name($x)
return element r {
           element tag_name  {$i/ROW/TAG_RENAME/text()}
        , element tag_value {$x/text()}
         }'
passing case when t.properties is null then null else xmltype ('<?xml version="1.0" encoding="iso-8859-1" ?>'||t.properties)end
columns tag_name varchar2(30) path 'tag_name',
                tag_value varchar2(30) path 'tag_value'                
)x on 1=1 and x.tag_value is not null;

As output I got

So it is almost working. The problem is that I'd like to have each ID on a single line. I tried with listagg and it's working but it's slow. Is there a way to rename TAGs directly inside XQuery?

You may use XQuery to modify 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

fiddle

UPD: If you want to remove unmatched tags or tags with no value you may use multiple modifications.

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

which for this sample data

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

returns

fiddle

You could do it by generating a 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
;