Forum Stats

  • 3,851,765 Users
  • 2,264,024 Discussions
  • 7,904,843 Comments

Discussions

Extract a specific value from a CLOB field and process that value in Oracle.

Shiva509
Shiva509 Member Posts: 7
edited Jan 7, 2020 4:37AM in SQL & PL/SQL

Below is the piece of clob data.

<?xml version="1.0" encoding="windows-1252"?>

<XMI xmi.version="1.1" xmlns:UML="omg.org/UML1.3" timestamp="2019-12-27 05:30:16">

<XMI.header>

<XMI.documentation>

<XMI.exporter>Enterprise Architect</XMI.exporter>

<XMI.exporterVersion>2.5</XMI.exporterVersion>

        </XMI.documentation>

</XMI.header>

    <XMI.content>

<UML:Model name="EA Model" xmi.id="01912248_FDE35517">

<UML:Namespace.ownedElement>

<UML:Class name="EARootClass" xmi.id="11111111_A7F4_CB0AA00" isRoot="true" isLeaf="false" isAbstract="false"/>

</UML:Namespace.ownedElement>

        </UML:Model>

    </XMI.content>

</XMI>

Need to extract attribute name from UML Class i.e "EARootClass" and process these value using a another table(Assume that there is a table that contains abbreviation of the words).

My final output would be like

<?xml version="1.0" encoding="windows-1252"?>

<XMI xmi.version="1.1" xmlns:UML="omg.org/UML1.3" timestamp="2019-12-27 05:30:16">

<XMI.header>

<XMI.documentation>

<XMI.exporter>Enterprise Architect</XMI.exporter>

<XMI.exporterVersion>2.5</XMI.exporterVersion>

        </XMI.documentation>

</XMI.header>

    <XMI.content>

<UML:Model name="EA Model" xmi.id="01912248_FDE35517">

<UML:Namespace.ownedElement>

<UML:Class name="EA_RT_CLS" xmi.id="11111111_A7F4_CB0AA00" isRoot="true" isLeaf="false" isAbstract="false"/>

</UML:Namespace.ownedElement>

        </UML:Model>

    </XMI.content>

</XMI>

Thanks in advance,

Shiva

Tagged:
Frank KulashShiva509

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,748 Blue Diamond
    edited Jan 5, 2020 8:14AM Answer ✓

    This is not a trivial task - the constructs in this problem are quite complicated.  The idea of this forum is to try to help people with their problems, not provide a free consultancy / coding service for people, so next time show your attempts at your problem and explain why you are doing this.

    -- Simulate table with original xml data

    create table t_data as

      select 1 as id, to_clob(

      '<?xml version="1.0" encoding="windows-1252"?>

      <XMI xmi.version="1.1" xmlns:UML="omg.org/UML1.3" timestamp="2019-12-27 05:30:16">

           <XMI.header>

                <XMI.documentation>

                     <XMI.exporter>Enterprise Architect</XMI.exporter>

                     <XMI.exporterVersion>2.5</XMI.exporterVersion>

              </XMI.documentation>

           </XMI.header>

          <XMI.content>

                <UML:Model name="EA Model" xmi.id="01912248_FDE35517">

                     <UML:Namespace.ownedElement>

                          <UML:Class name="EARootClass" xmi.id="11111111_A7F4_CB0AA00" isRoot="true" isLeaf="false" isAbstract="false"/>

                          <UML:Package name="Domain Model" xmi.id="EAPK_01912248_C12A" isRoot="false" isLeaf="false" isAbstract="false" visibility="public"/>

                          <UML:Class name="VacationEntitlementMatrixSettings" xmi.id="EAID_620D1EA2_D481_4741" visibility="public" namespace="EAPK_01912248_C12A" isRoot="false" isLeaf="false" isAbstract="false" isActive="false">

                                <UML:ModelElement.taggedValue>

                                    <UML:TaggedValue tag="isSpecification" value="false"/>

                                    <UML:TaggedValue tag="ea_stype" value="Class"/>

                                    <UML:TaggedValue tag="ea_ntype" value="0"/>

                                    <UML:TaggedValue tag="version" value="1.0"/>

                                    <UML:TaggedValue tag="package" value="EAPK_01912248_C12A"/>

                               </UML:ModelElement.taggedValue>

                          </UML:Class>

                     </UML:Namespace.ownedElement>

              </UML:Model>

          </XMI.content>

      </XMI>') the_clob

      from dual

      union all

      select 2, to_clob(

      '<?xml version="1.0" encoding="windows-1252"?>

      <XMI xmi.version="1.1" xmlns:UML="omg.org/UML1.3" timestamp="2019-12-27 05:30:16">

           <XMI.header>

                <XMI.documentation>

                     <XMI.exporter>Enterprise Architect</XMI.exporter>

                     <XMI.exporterVersion>2.5</XMI.exporterVersion>

              </XMI.documentation>

           </XMI.header>

          <XMI.content>

                <UML:Model name="EA Model" xmi.id="01912248_FDE35517">

                     <UML:Namespace.ownedElement>

                          <UML:Class name="RootUnmatchClass" xmi.id="11111111_A7F4_CB0AA00" isRoot="true" isLeaf="false" isAbstract="false"/>

                          <UML:Package name="Domain Model" xmi.id="EAPK_01912248_C12A" isRoot="false" isLeaf="false" isAbstract="false" visibility="public"/>

                          <UML:Class name="MatrixEntitlementSettingsMatrix" xmi.id="EAID_620D1EA2_D481_4741" visibility="public" namespace="EAPK_01912248_C12A" isRoot="false" isLeaf="false" isAbstract="false" isActive="false">

                                <UML:ModelElement.taggedValue>

                                    <UML:TaggedValue tag="isSpecification" value="false"/>

                                    <UML:TaggedValue tag="ea_stype" value="Class"/>

                                    <UML:TaggedValue tag="ea_ntype" value="0"/>

                                    <UML:TaggedValue tag="version" value="1.0"/>

                                    <UML:TaggedValue tag="package" value="EAPK_01912248_C12A"/>

                               </UML:ModelElement.taggedValue>

                          </UML:Class>

                          <UML:Class name="RootClassRootEA" xmi.id="11111111_A7F4_CB0AA00" isRoot="true" isLeaf="false" isAbstract="false"/>                     

                     </UML:Namespace.ownedElement>

              </UML:Model>

          </XMI.content>

      </XMI>') -- another test

      from dual;

    -- Code for doing the task

    with classnames as (

      select d.id, x.*

      from   t_data d

        ,    XMLTable(    

               XMLNamespaces(

                 'omg.org/UML1.3' as "UML"

               ),

               '/XMI/XMI.content/UML:Model/UML:Namespace.ownedElement/UML:Class'

               passing XMLType(d.the_clob)

               columns

                 node_pos for ordinality

               , classname varchar2(100) path '@name'

             ) x

    )

    , replacements as (

      select x.*

           , row_number() over (partition by id, node_pos order by pos) rn -- helps to find root

           , row_number() over (partition by id, node_pos order by pos desc) rnr -- helps to find leaf

           , '_'||case when length(col_3) < length(col_4) then col_3 else col_4 end col_r  -- Find shortest replacement

      from (

        select c.*, a.*, instr(c.classname, a.col_1) pos

        from classnames c

        left join abbr_table a on instr(c.classname, a.col_1) > 0

      ) x 

    )

    --select * from replacements order by id,

    , recurse(id, node_pos, classname, result, col_1, col_r, pos, rn, rnr) as (  -- recursive multi-replace

      select id, node_pos, classname, replace(classname, col_1, col_r), col_1, col_r, pos, rn, rnr

      from   replacements

      where  rn = 1    -- Anchor

      union all

      select t.id, a.node_pos, a.classname, replace(t.result, a.col_1, a.col_r), a.col_1, a.col_r, a.pos, a.rn, a.rnr

      from   recurse t

      join   replacements a on a.id = t.id and a.node_pos = t.node_pos and a.rn = t.rn + 1 --and

    )

    --select * from recurse where rnr = 1 order by id, node_pos

    , replace_xml as (

      select id, 

             XMLElement("replace",     -- Construct XML containing replacements, to be passed to XQuery for update

               XMLAgg(        

                 XMLElement("val",

                   XMLAttributes(classname as "old_val"),   -- what we're searching for, for reference

                   case when length(ltrim(result, '_')) <= 30 then ltrim(result, '_') else classname end -- don't replace if greater than 30 chars, but still need ordinal placeholder

                 )            

                 order by node_pos  -- Must order nodes in ordinal position matching source

               )

             ) replace_xml

      from   recurse t

      where rnr = 1

      group by id

    )

    select /*+ no_xml_query_rewrite */  --  force functional evaluation, otherwise optimiser rewrites and mangles

           d.id, 

           XMlSerialize(

             document

             XMLQuery(

              'declare namespace UML="omg.org/UML1.3"; (: :)

               copy $tmp := .

               modify

               (for $i at $p in $tmp/XMI/XMI.content/UML:Model/UML:Namespace.ownedElement/UML:Class

                  return replace value of node $i/@name with $r/replace/val[position()=$p]

               )

               return $tmp'

               passing XMLType(d.the_clob), r.replace_xml as "r"

               returning content

             )

             indent size = 2

           ) new_clob

    from t_data d

    join replace_xml r on r.id = d.id;

                       

    IDNEW_CLOB
    1

    <?xml version="1.0" encoding="UTF-8"?>

    <XMI xmi.version="1.1" xmlns:UML="omg.org/UML1.3" timestamp="2019-12-27 05:30:16">

      <XMI.header>

        <XMI.documentation>

          <XMI.exporter>Enterprise Architect</XMI.exporter>

          <XMI.exporterVersion>2.5</XMI.exporterVersion>

        </XMI.documentation>

      </XMI.header>

      <XMI.content>

        <UML:Model name="EA Model" xmi.id="01912248_FDE35517">

          <UML:Namespace.ownedElement>

            <UML:Class name="EA_RT_CL" xmi.id="11111111_A7F4_CB0AA00" isRoot="true" isLeaf="false" isAbstract="false"/>

            <UML:Package name="Domain Model" xmi.id="EAPK_01912248_C12A" isRoot="false" isLeaf="false" isAbstract="false" visibility="public"/>

            <UML:Class name="VA_EN_MT_SE" xmi.id="EAID_620D1EA2_D481_4741" visibility="public" namespace="EAPK_01912248_C12A" isRoot="false" isLeaf="false" isAbstract="false" isActive="false">

              <UML:ModelElement.taggedValue>

                <UML:TaggedValue tag="isSpecification" value="false"/>

                <UML:TaggedValue tag="ea_stype" value="Class"/>

                <UML:TaggedValue tag="ea_ntype" value="0"/>

                <UML:TaggedValue tag="version" value="1.0"/>

                <UML:TaggedValue tag="package" value="EAPK_01912248_C12A"/>

              </UML:ModelElement.taggedValue>

            </UML:Class>

          </UML:Namespace.ownedElement>

        </UML:Model>

      </XMI.content>

    </XMI>

    2

    <?xml version="1.0" encoding="UTF-8"?>

    <XMI xmi.version="1.1" xmlns:UML="omg.org/UML1.3" timestamp="2019-12-27 05:30:16">

      <XMI.header>

        <XMI.documentation>

          <XMI.exporter>Enterprise Architect</XMI.exporter>

          <XMI.exporterVersion>2.5</XMI.exporterVersion>

        </XMI.documentation>

      </XMI.header>

      <XMI.content>

        <UML:Model name="EA Model" xmi.id="01912248_FDE35517">

          <UML:Namespace.ownedElement>

            <UML:Class name="RTUnmatch_CL" xmi.id="11111111_A7F4_CB0AA00" isRoot="true" isLeaf="false" isAbstract="false"/>

            <UML:Package name="Domain Model" xmi.id="EAPK_01912248_C12A" isRoot="false" isLeaf="false" isAbstract="false" visibility="public"/>

            <UML:Class name="MT_EN_SE_MT" xmi.id="EAID_620D1EA2_D481_4741" visibility="public" namespace="EAPK_01912248_C12A" isRoot="false" isLeaf="false" isAbstract="false" isActive="false">

              <UML:ModelElement.taggedValue>

                <UML:TaggedValue tag="isSpecification" value="false"/>

                <UML:TaggedValue tag="ea_stype" value="Class"/>

                <UML:TaggedValue tag="ea_ntype" value="0"/>

                <UML:TaggedValue tag="version" value="1.0"/>

                <UML:TaggedValue tag="package" value="EAPK_01912248_C12A"/>

              </UML:ModelElement.taggedValue>

            </UML:Class>

            <UML:Class name="RT_CL_RTEA" xmi.id="11111111_A7F4_CB0AA00" isRoot="true" isLeaf="false" isAbstract="false"/>

          </UML:Namespace.ownedElement>

        </UML:Model>

      </XMI.content>

    </XMI>

Answers

  • mNem
    mNem Member Posts: 1,380 Gold Trophy
    edited Dec 31, 2019 11:38AM
    d58d2db4-f645-41ff-b29d-1c0b08b0676e wrote:Need to extract attribute name from UML Class i.e "EARootClass" and process these value using a another table(Assume that there is a table that contains abbreviation of the words).

    Are you are looking for replacing the attribute value EARootClass to the corresponding value found in the lookup table?

    It may help if you could post some rows from the lookup table including the row for EARootClass.

    Frank KulashShiva509
  • Paulzip
    Paulzip Member Posts: 8,748 Blue Diamond
    edited Dec 31, 2019 11:48AM

    How to read the data...

    with data(the_clob) as (

      select '<?xml version="1.0" encoding="windows-1252"?>

      <XMI xmi.version="1.1" xmlns:UML="omg.org/UML1.3" timestamp="2019-12-27 05:30:16">

        <XMI.header>

          <XMI.documentation>

            <XMI.exporter>Enterprise Architect</XMI.exporter>

            <XMI.exporterVersion>2.5</XMI.exporterVersion>

          </XMI.documentation>

        </XMI.header>

        <XMI.content>

          <UML:Model name="EA Model" xmi.id="01912248_FDE35517">

            <UML:Namespace.ownedElement>

              <UML:Class name="EARootClass" xmi.id="11111111_A7F4_CB0AA00" isRoot="true" isLeaf="false" isAbstract="false"/>

            </UML:Namespace.ownedElement>

          </UML:Model>

        </XMI.content>

      </XMI>'

      from dual

    )

    select x.ClassName

    from   data d,

           XMLTable(

             XMLNamespaces(

               'omg.org/UML1.3' as "UML"

             ),

             '/XMI/XMI.content/UML:Model/UML:Namespace.ownedElement/UML:Class'

             passing XMLType(d.the_clob)

             columns

               ClassName xmltype path '@name'

           ) x;

    How to update it...

    with data(the_clob) as (

      select '<?xml version="1.0" encoding="windows-1252"?>

      <XMI xmi.version="1.1" xmlns:UML="omg.org/UML1.3" timestamp="2019-12-27 05:30:16">

        <XMI.header>

          <XMI.documentation>

            <XMI.exporter>Enterprise Architect</XMI.exporter>

            <XMI.exporterVersion>2.5</XMI.exporterVersion>

          </XMI.documentation>

        </XMI.header>

        <XMI.content>

          <UML:Model name="EA Model" xmi.id="01912248_FDE35517">

            <UML:Namespace.ownedElement>

              <UML:Class name="EARootClass" xmi.id="11111111_A7F4_CB0AA00" isRoot="true" isLeaf="false" isAbstract="false"/>

            </UML:Namespace.ownedElement>

          </UML:Model>

        </XMI.content>

      </XMI>'

      from dual

    )    

    select XMlSerialize(

             document

             XMLQuery(

              'declare namespace UML="omg.org/UML1.3"; (: :)

               copy $tmp := .

               modify

               (for $i in $tmp/XMI/XMI.content/UML:Model/UML:Namespace.ownedElement/UML:Class

                  return replace value of node $i/@name with $n

               )

               return $tmp'

               passing XMLType(d.the_clob), 'EA_RT_CLS' as "n"

               returning content

             )

             indent size = 2

           ) the_clob

    from data d;

    <?xml version="1.0" encoding="UTF-8"?>

    <XMI xmi.version="1.1" xmlns:UML="omg.org/UML1.3" timestamp="2019-12-27 05:30:16">

      <XMI.header>

        <XMI.documentation>

          <XMI.exporter>Enterprise Architect</XMI.exporter>

          <XMI.exporterVersion>2.5</XMI.exporterVersion>

        </XMI.documentation>

      </XMI.header>

      <XMI.content>

        <UML:Model name="EA Model" xmi.id="01912248_FDE35517">

          <UML:Namespace.ownedElement>

            <UML:Class name="EA_RT_CLS" xmi.id="11111111_A7F4_CB0AA00" isRoot="true" isLeaf="false" isAbstract="false"/>

          </UML:Namespace.ownedElement>

        </UML:Model>

      </XMI.content>

    </XMI>

    EA_RT_CLS can be passed from a table column value.

    To make it into an update based on another table, I'd probably use a merge, but you didn't give any table details, so I'll leave that to you, that should be pretty easy for you to do based on above.

    Shiva509
  • Shiva509
    Shiva509 Member Posts: 7
    edited Jan 3, 2020 1:39PM

    Thanks for the solution.

    Here is the table structure and the data looks like below.

    pastedImage_0.png

    For EARootClass, we have to extract the words from string (In the clob data we might have multiple Classes. Need to iterate for each Class attribute) and match each word with above look up table. If there is any match we have to get the acronym of the word otherwise leave the word as is. Here, Col_3 and Col_4 are the acronyms for Col_1, we can take any one from Col_3 or Col_4 for the matching word(Make sure the whole new string should not exceed 30 characters).

    After matching all extracted words from a string with the look up table, merge each acronym by separating "_"

    Below data contains two Classes(Similar way we have so many classes in clob data). Process each class name by matching relevant acronym from the look up table.

    <?xml version="1.0" encoding="windows-1252"?>

    <XMI xmi.version="1.1" xmlns:UML="omg.org/UML1.3" timestamp="2019-12-27 05:30:16">

         <XMI.header>

              <XMI.documentation>

                   <XMI.exporter>Enterprise Architect</XMI.exporter>

                   <XMI.exporterVersion>2.5</XMI.exporterVersion>

            </XMI.documentation>

         </XMI.header>

        <XMI.content>

              <UML:Model name="EA Model" xmi.id="01912248_FDE35517">

                   <UML:Namespace.ownedElement>

                        <UML:Class name="EA Root Class" xmi.id="11111111_A7F4_CB0AA00" isRoot="true" isLeaf="false" isAbstract="false"/>

                        <UML:Package name="Domain Model" xmi.id="EAPK_01912248_C12A" isRoot="false" isLeaf="false" isAbstract="false" visibility="public"/>

                        <UML:Class name="Vacation Entitlement Matrix Settings" xmi.id="EAID_620D1EA2_D481_4741" visibility="public" namespace="EAPK_01912248_C12A" isRoot="false" isLeaf="false" isAbstract="false" isActive="false">

                              <UML:ModelElement.taggedValue>

                                  <UML:TaggedValue tag="isSpecification" value="false"/>

                                  <UML:TaggedValue tag="ea_stype" value="Class"/>

                                  <UML:TaggedValue tag="ea_ntype" value="0"/>

                                  <UML:TaggedValue tag="version" value="1.0"/>

                                  <UML:TaggedValue tag="package" value="EAPK_01912248_C12A"/>

                             </UML:ModelElement.taggedValue>

                        </UML:Class>

                   </UML:Namespace.ownedElement>

            </UML:Model>

        </XMI.content>

    </XMI>

    Example:

    String EARootClass

    Need to extract "EA", "Root" and "Class" from the given string(EARootClass) then match each word with columns Col_3 or Col_4 from look up table( "EA" is not there in the look up table so, leave as is).

    Final output may looks like

    EA_ROOT_CLAS or   -- New String Length 12

    EA_RT_CLAS       or   -- New String Length 10

    EA_ROOT_CL      or   -- New String Length 10

    EA_RT_CL            or   -- New String Length 08

    The above all four are possible matches for string EARootClass(if and only if the length of the new string should not exceed 30 characters). So, we can take any one string from the four and replace same in place of the original string.

    Please let me know in case if you need any more clarification on this.

    Thanks in Advance.

  • Shiva509
    Shiva509 Member Posts: 7
    edited Jan 2, 2020 2:31AM

    @mNem, Thanks for looking into this.

    I have provided the sample look up table data. in response to Paulzip.

    Thanks in Advance.

  • Shiva509
    Shiva509 Member Posts: 7
    edited Jan 3, 2020 6:21AM

    Can any one please help me here?

  • Paulzip
    Paulzip Member Posts: 8,748 Blue Diamond
    edited Jan 3, 2020 8:56AM
    Shiva509 wrote:mNem, Thanks for looking into this.I have provided the sample look up table data. in response to Paulzip.Thanks in Advance.

    You haven't provided sample data, you provided a screenshot. We cannot write and test queries against screenshots. Can you?  You have also moved the goal posts on your original description.

    Please read the forum FAQ, it's very clear that if you want help, you'll need to provide table creation scripts and their sample data (as inserts), so that we can test things.  We don't have time to setup your situation manually, we are all very busy and our time is valuable.

  • Shiva509
    Shiva509 Member Posts: 7
    edited Jan 3, 2020 9:29AM

    I am new to the forum.

    Apologies for not providing the data and moved to other goal. The actual requirement is provided in

    Table Script:

    create table abbr_table(col_1 varchar2(50),col_2 varchar2(5),col_3 varchar2(50),col_4 varchar2(20));

    Data:

    insert into abbr_table values('Matrix','N','MTRX','MT');

    insert into abbr_table values('Abandon','N','ABAN','AB');

    insert into abbr_table values('Accident','N','ACC','AC');

    insert into abbr_table values('Accidents','N','ACC','AC');

    insert into abbr_table values('American National Standards Institute','N','ANSI','AN');

    insert into abbr_table values('Build','N','BLD','BU');

    insert into abbr_table values('Bulk','N','BULK','BL');

    insert into abbr_table values('Bulletin','N','BUL','BU');

    insert into abbr_table values('Class','N','CLAS','CL');

    insert into abbr_table values('Entitlement','N','ENTL','EN');

    insert into abbr_table values('Root','N','ROOT','RT');

    insert into abbr_table values('Settings','N','SETG','SE');

    insert into abbr_table values('Vacation','N','VAC','VA');

    Thanks in advance.

  • Shiva509
    Shiva509 Member Posts: 7
    edited Jan 4, 2020 5:12AM

    Hi @Paulzip, Do you need any additional details? Please let me know.

    Thanks.

  • Paulzip
    Paulzip Member Posts: 8,748 Blue Diamond
    edited Jan 5, 2020 8:14AM Answer ✓

    This is not a trivial task - the constructs in this problem are quite complicated.  The idea of this forum is to try to help people with their problems, not provide a free consultancy / coding service for people, so next time show your attempts at your problem and explain why you are doing this.

    -- Simulate table with original xml data

    create table t_data as

      select 1 as id, to_clob(

      '<?xml version="1.0" encoding="windows-1252"?>

      <XMI xmi.version="1.1" xmlns:UML="omg.org/UML1.3" timestamp="2019-12-27 05:30:16">

           <XMI.header>

                <XMI.documentation>

                     <XMI.exporter>Enterprise Architect</XMI.exporter>

                     <XMI.exporterVersion>2.5</XMI.exporterVersion>

              </XMI.documentation>

           </XMI.header>

          <XMI.content>

                <UML:Model name="EA Model" xmi.id="01912248_FDE35517">

                     <UML:Namespace.ownedElement>

                          <UML:Class name="EARootClass" xmi.id="11111111_A7F4_CB0AA00" isRoot="true" isLeaf="false" isAbstract="false"/>

                          <UML:Package name="Domain Model" xmi.id="EAPK_01912248_C12A" isRoot="false" isLeaf="false" isAbstract="false" visibility="public"/>

                          <UML:Class name="VacationEntitlementMatrixSettings" xmi.id="EAID_620D1EA2_D481_4741" visibility="public" namespace="EAPK_01912248_C12A" isRoot="false" isLeaf="false" isAbstract="false" isActive="false">

                                <UML:ModelElement.taggedValue>

                                    <UML:TaggedValue tag="isSpecification" value="false"/>

                                    <UML:TaggedValue tag="ea_stype" value="Class"/>

                                    <UML:TaggedValue tag="ea_ntype" value="0"/>

                                    <UML:TaggedValue tag="version" value="1.0"/>

                                    <UML:TaggedValue tag="package" value="EAPK_01912248_C12A"/>

                               </UML:ModelElement.taggedValue>

                          </UML:Class>

                     </UML:Namespace.ownedElement>

              </UML:Model>

          </XMI.content>

      </XMI>') the_clob

      from dual

      union all

      select 2, to_clob(

      '<?xml version="1.0" encoding="windows-1252"?>

      <XMI xmi.version="1.1" xmlns:UML="omg.org/UML1.3" timestamp="2019-12-27 05:30:16">

           <XMI.header>

                <XMI.documentation>

                     <XMI.exporter>Enterprise Architect</XMI.exporter>

                     <XMI.exporterVersion>2.5</XMI.exporterVersion>

              </XMI.documentation>

           </XMI.header>

          <XMI.content>

                <UML:Model name="EA Model" xmi.id="01912248_FDE35517">

                     <UML:Namespace.ownedElement>

                          <UML:Class name="RootUnmatchClass" xmi.id="11111111_A7F4_CB0AA00" isRoot="true" isLeaf="false" isAbstract="false"/>

                          <UML:Package name="Domain Model" xmi.id="EAPK_01912248_C12A" isRoot="false" isLeaf="false" isAbstract="false" visibility="public"/>

                          <UML:Class name="MatrixEntitlementSettingsMatrix" xmi.id="EAID_620D1EA2_D481_4741" visibility="public" namespace="EAPK_01912248_C12A" isRoot="false" isLeaf="false" isAbstract="false" isActive="false">

                                <UML:ModelElement.taggedValue>

                                    <UML:TaggedValue tag="isSpecification" value="false"/>

                                    <UML:TaggedValue tag="ea_stype" value="Class"/>

                                    <UML:TaggedValue tag="ea_ntype" value="0"/>

                                    <UML:TaggedValue tag="version" value="1.0"/>

                                    <UML:TaggedValue tag="package" value="EAPK_01912248_C12A"/>

                               </UML:ModelElement.taggedValue>

                          </UML:Class>

                          <UML:Class name="RootClassRootEA" xmi.id="11111111_A7F4_CB0AA00" isRoot="true" isLeaf="false" isAbstract="false"/>                     

                     </UML:Namespace.ownedElement>

              </UML:Model>

          </XMI.content>

      </XMI>') -- another test

      from dual;

    -- Code for doing the task

    with classnames as (

      select d.id, x.*

      from   t_data d

        ,    XMLTable(    

               XMLNamespaces(

                 'omg.org/UML1.3' as "UML"

               ),

               '/XMI/XMI.content/UML:Model/UML:Namespace.ownedElement/UML:Class'

               passing XMLType(d.the_clob)

               columns

                 node_pos for ordinality

               , classname varchar2(100) path '@name'

             ) x

    )

    , replacements as (

      select x.*

           , row_number() over (partition by id, node_pos order by pos) rn -- helps to find root

           , row_number() over (partition by id, node_pos order by pos desc) rnr -- helps to find leaf

           , '_'||case when length(col_3) < length(col_4) then col_3 else col_4 end col_r  -- Find shortest replacement

      from (

        select c.*, a.*, instr(c.classname, a.col_1) pos

        from classnames c

        left join abbr_table a on instr(c.classname, a.col_1) > 0

      ) x 

    )

    --select * from replacements order by id,

    , recurse(id, node_pos, classname, result, col_1, col_r, pos, rn, rnr) as (  -- recursive multi-replace

      select id, node_pos, classname, replace(classname, col_1, col_r), col_1, col_r, pos, rn, rnr

      from   replacements

      where  rn = 1    -- Anchor

      union all

      select t.id, a.node_pos, a.classname, replace(t.result, a.col_1, a.col_r), a.col_1, a.col_r, a.pos, a.rn, a.rnr

      from   recurse t

      join   replacements a on a.id = t.id and a.node_pos = t.node_pos and a.rn = t.rn + 1 --and

    )

    --select * from recurse where rnr = 1 order by id, node_pos

    , replace_xml as (

      select id, 

             XMLElement("replace",     -- Construct XML containing replacements, to be passed to XQuery for update

               XMLAgg(        

                 XMLElement("val",

                   XMLAttributes(classname as "old_val"),   -- what we're searching for, for reference

                   case when length(ltrim(result, '_')) <= 30 then ltrim(result, '_') else classname end -- don't replace if greater than 30 chars, but still need ordinal placeholder

                 )            

                 order by node_pos  -- Must order nodes in ordinal position matching source

               )

             ) replace_xml

      from   recurse t

      where rnr = 1

      group by id

    )

    select /*+ no_xml_query_rewrite */  --  force functional evaluation, otherwise optimiser rewrites and mangles

           d.id, 

           XMlSerialize(

             document

             XMLQuery(

              'declare namespace UML="omg.org/UML1.3"; (: :)

               copy $tmp := .

               modify

               (for $i at $p in $tmp/XMI/XMI.content/UML:Model/UML:Namespace.ownedElement/UML:Class

                  return replace value of node $i/@name with $r/replace/val[position()=$p]

               )

               return $tmp'

               passing XMLType(d.the_clob), r.replace_xml as "r"

               returning content

             )

             indent size = 2

           ) new_clob

    from t_data d

    join replace_xml r on r.id = d.id;

                       

    IDNEW_CLOB
    1

    <?xml version="1.0" encoding="UTF-8"?>

    <XMI xmi.version="1.1" xmlns:UML="omg.org/UML1.3" timestamp="2019-12-27 05:30:16">

      <XMI.header>

        <XMI.documentation>

          <XMI.exporter>Enterprise Architect</XMI.exporter>

          <XMI.exporterVersion>2.5</XMI.exporterVersion>

        </XMI.documentation>

      </XMI.header>

      <XMI.content>

        <UML:Model name="EA Model" xmi.id="01912248_FDE35517">

          <UML:Namespace.ownedElement>

            <UML:Class name="EA_RT_CL" xmi.id="11111111_A7F4_CB0AA00" isRoot="true" isLeaf="false" isAbstract="false"/>

            <UML:Package name="Domain Model" xmi.id="EAPK_01912248_C12A" isRoot="false" isLeaf="false" isAbstract="false" visibility="public"/>

            <UML:Class name="VA_EN_MT_SE" xmi.id="EAID_620D1EA2_D481_4741" visibility="public" namespace="EAPK_01912248_C12A" isRoot="false" isLeaf="false" isAbstract="false" isActive="false">

              <UML:ModelElement.taggedValue>

                <UML:TaggedValue tag="isSpecification" value="false"/>

                <UML:TaggedValue tag="ea_stype" value="Class"/>

                <UML:TaggedValue tag="ea_ntype" value="0"/>

                <UML:TaggedValue tag="version" value="1.0"/>

                <UML:TaggedValue tag="package" value="EAPK_01912248_C12A"/>

              </UML:ModelElement.taggedValue>

            </UML:Class>

          </UML:Namespace.ownedElement>

        </UML:Model>

      </XMI.content>

    </XMI>

    2

    <?xml version="1.0" encoding="UTF-8"?>

    <XMI xmi.version="1.1" xmlns:UML="omg.org/UML1.3" timestamp="2019-12-27 05:30:16">

      <XMI.header>

        <XMI.documentation>

          <XMI.exporter>Enterprise Architect</XMI.exporter>

          <XMI.exporterVersion>2.5</XMI.exporterVersion>

        </XMI.documentation>

      </XMI.header>

      <XMI.content>

        <UML:Model name="EA Model" xmi.id="01912248_FDE35517">

          <UML:Namespace.ownedElement>

            <UML:Class name="RTUnmatch_CL" xmi.id="11111111_A7F4_CB0AA00" isRoot="true" isLeaf="false" isAbstract="false"/>

            <UML:Package name="Domain Model" xmi.id="EAPK_01912248_C12A" isRoot="false" isLeaf="false" isAbstract="false" visibility="public"/>

            <UML:Class name="MT_EN_SE_MT" xmi.id="EAID_620D1EA2_D481_4741" visibility="public" namespace="EAPK_01912248_C12A" isRoot="false" isLeaf="false" isAbstract="false" isActive="false">

              <UML:ModelElement.taggedValue>

                <UML:TaggedValue tag="isSpecification" value="false"/>

                <UML:TaggedValue tag="ea_stype" value="Class"/>

                <UML:TaggedValue tag="ea_ntype" value="0"/>

                <UML:TaggedValue tag="version" value="1.0"/>

                <UML:TaggedValue tag="package" value="EAPK_01912248_C12A"/>

              </UML:ModelElement.taggedValue>

            </UML:Class>

            <UML:Class name="RT_CL_RTEA" xmi.id="11111111_A7F4_CB0AA00" isRoot="true" isLeaf="false" isAbstract="false"/>

          </UML:Namespace.ownedElement>

        </UML:Model>

      </XMI.content>

    </XMI>

  • Shiva509
    Shiva509 Member Posts: 7
    edited Jan 7, 2020 4:37AM

    Thank you very much @Paulzip!!