CODEWITHSUNDEEP
xmloracle-databasexquery
user1283565
user1283565

Reputation: 31

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:

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:

ID PROPERTIES
1 </Fields><TAG1>123</TAG1><TAG2>345</TAG2></Fields>
2 </Fields><TAG1>999</TAG1><TAG3>XXX</TAG3></Fields>

Table2:

TAG_NAME TAG_RENAME
TAG1 NEW_TAG1
TAG2 NEW_TAG2
TAG3 NEW_TAG3

Output needed:

ID NEW_PROPERTIES
1 </Fields><NEW_TAG1>123</NEW_TAG1><NEW_TAG2>345</NEW_TAG2></Fields>
3 </Fields><NEW_TAG1>999</NEW_TAG1><NEW_TAG3>XXX</NEW_TAG3></Fields>

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

ID TAG_NAME TAG_VALUE
1 NEW_TAG1 123
1 NEW_TAG2 345
2 NEW_TAG1 999
2 NEW_TAG3 XXX

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?

Upvotes: 0

Views: 151

Answers (2)

astentx
astentx

Reputation: 6751

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
ID VAL RENAMED
1 <Fields><TAG1>123</TAG1><TAG2>345</TAG2></Fields> <Fields><NEW_TAG1>123</NEW_TAG1><NEW_TAG2>345</NEW_TAG2></Fields>
2 <Fields><TAG1>999</TAG1><TAG3>XXX</TAG3></Fields> <Fields><NEW_TAG1>999</NEW_TAG1><NEW_TAG3>XXX</NEW_TAG3></Fields>

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

ID VAL RENAMED
1 <Fields><TAG1>123</TAG1><TAG2>345</TAG2><TAG_WITH_NO_VALUE/></Fields> <Fields><NEW_TAG2>345</NEW_TAG2></Fields>
2 <Fields><TAG1>999</TAG1><TAG3>XXX</TAG3></Fields> <Fields><NEW_TAG3>XXX</NEW_TAG3></Fields>

fiddle

Upvotes: 1

p3consulting
p3consulting

Reputation: 4650

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
;

Upvotes: 0

Related Questions