11 Replies Latest reply on Oct 18, 2018 1:07 PM by Big Pang

    how to update XML node with PL/SQL

    Big Pang

      I can't figure out how to put PL/SQL function inside XMLQuery update statement.

       

      i.e

       

      SELECT

      XMLQuery('copy $i := $p1 modify

                          ( for $j in $i//Company return replace value of node $j with "my PL/SQL function (this node)" )

                        return $i'

                        PASSING

                                    MyData AS "p1"

                          RETURNING CONTENT)

      from MyTable;

       

      BTW, I tried '||' , it doesn't work.

        • 1. Re: how to update XML node with PL/SQL
          cormaco

          The simple answer is: you can't call PL/SQL within an xquery statement.

          What does your PL/SQL function calculate, there might be a xquery equivalent?

           

          If you have call this PL/SQL function you can try this:

          Extract the values you want to change with XMLTABLE, call your function on them and pack the old and new values in an xmlelement.

          Use this xmlelement as parameter in your xmlquery call to update your original xml.

           

          Here is an example:

          with input(xmldata) as (
          select xmltype(
          '<Companies>
              <Company>Apple</Company>
              <Company>Google</Company>
              <Company>Microsoft</Company>
          </Companies>') 
          from dual),
          updatelist as (
          select xmldata,xmlagg(xmlelement("update",xmlattributes(cmp_old,UPPER(cmp_old) as cmp_new))) as updates
          from input,xmltable(
              '/Companies/Company'
              passing xmldata
              columns
                  cmp_old varchar2(20) path '.'
          ))
          select 
          xmlserialize(document 
              xmlquery(
                  'copy $tmp := .
                   modify
                   (for $u in $upd/update
                      return replace value of node $tmp/Companies/Company[.=$u/@CMP_OLD] with $u/@CMP_NEW
                   )
                   return $tmp
                   '
                   passing xmldata,updates as "upd"
                   returning content)
          as varchar2(150) indent) as updated_xml       
          from updatelist
          
          

           

          Result:

          <Companies>
            <Company>APPLE</Company>
            <Company>GOOGLE</Company>
            <Company>MICROSOFT</Company>
          </Companies>
          
          
          

           

          This is how the updatelist looks like:

          <update CMP_OLD="Apple" CMP_NEW="APPLE"></update>
          <update CMP_OLD="Google" CMP_NEW="GOOGLE"></update>
          <update CMP_OLD="Microsoft" CMP_NEW="MICROSOFT"></update>
          
          
          • 2. Re: how to update XML node with PL/SQL
            Big Pang

            without PL/SQL, it is too weak to run xquery update with literal values or simple xquery functions. I thought your approach of get interested XML portion, update it and write back to original one. But I have to say your XML SQL skill with one statement is GOLD. I am not happy with xmldom speed so I need to test xquery update and compare them. Thanks Cormaco!

            • 3. Re: how to update XML node with PL/SQL
              cormaco

              But I have to say your XML SQL skill with one statement is GOLD.

              Actually I just noticed a serious oversight in my example, it works like this only on exactly one row of inputdata. That's what you get for oversimplifying your testdata.

              Here is the updated example for more than one row:

               

              with inputdata(pk,xmldata) as (
              select 1,xmltype(
              '<Companies>
                  <Company>Apple</Company>
                  <Company>Google</Company>
                  <Company>Microsoft</Company>
              </Companies>') 
              from dual union all
              select 2,xmltype(
              '<Companies>
                  <Company>Nokia</Company>
                  <Company>Motorola</Company>
                  <Company>Honor</Company>
              </Companies>') 
              from dual union all
              select 3,xmltype(
              '<Companies>
                  <Company>Chrysler</Company>
                  <Company>Mercedes</Company>
                  <Company>Toyota</Company>
              </Companies>') 
              from dual),
              updatelist as (
                  select pk,xmlagg(xmlelement("update",xmlattributes(cmp_old,UPPER(cmp_old) as cmp_new))) as updates
                  from inputdata,xmltable(
                      '/Companies/Company'
                      passing xmldata
                      columns
                          cmp_old varchar2(20) path '.'
                  ) 
                  group by pk
              )
              select 
              xmlserialize(document 
                  xmlquery(
                      'copy $tmp := .
                       modify
                       (for $u in $upd/update
                          return replace value of node $tmp/Companies/Company[.=$u/@CMP_OLD] with $u/@CMP_NEW
                       )
                       return $tmp
                       '
                       passing 
                          xmldata,
                          updates as "upd"
                       returning content)
              as varchar2(150) indent) as updated_xml       
              from updatelist join inputdata on updatelist.pk = inputdata.pk
              
              

               

              Output:

              <Companies>
                <Company>APPLE</Company>
                <Company>GOOGLE</Company>
                <Company>MICROSOFT</Company>
              </Companies>
              <Companies>
                <Company>NOKIA</Company>
                <Company>MOTOROLA</Company>
                <Company>HONOR</Company>
              </Companies>
              <Companies>
                <Company>CHRYSLER</Company>
                <Company>MERCEDES</Company>
                <Company>TOYOTA</Company>
              </Companies>
              
              
              
              • 4. Re: how to update XML node with PL/SQL
                Big Pang

                I would get chance to test it on multi-nodes xml data today :-)

                • 5. Re: how to update XML node with PL/SQL
                  Big Pang

                  It worked as expected.

                  • 6. Re: how to update XML node with PL/SQL
                    cormaco

                    That's good to hear.

                    • 7. Re: how to update XML node with PL/SQL
                      Big Pang

                      it would break if some XML node is empty. Finding way to workaround it. 

                       

                      SQL> with inputdata(pk,xmldata) as (

                        2  select 1,xmltype(

                        3  '<Companies>

                        4      <Company>Apple</Company>

                        5      <Company>Google</Company>

                        6      <Company>Microsoft</Company>

                        7  </Companies>')

                        8  from dual union all

                        9  select 2,xmltype(

                      10  '<Companies>

                      11      <Company>Nokia</Company>

                      12      <Company>Motorola</Company>

                      13      <Company>Honor</Company>

                      14  </Companies>')

                      15  from dual union all

                      16  select 3,xmltype(

                      17  '<Companies>

                      18      <Company/>

                      19      <Company>Mercedes</Company>

                      20      <Company>Toyota</Company>

                      21  </Companies>')

                      22  from dual),

                      23  updatelist as (

                      24      select pk,xmlagg(xmlelement("update",xmlattributes(cmp_old,UPPER(cmp_old) as cmp_new))) as updates

                      25      from inputdata,xmltable(

                      26          '/Companies/Company'

                      27          passing xmldata

                      28          columns

                      29              cmp_old varchar2(20) path '.'

                      30      )

                      31      group by pk

                      32  )

                      33  select

                      34  xmlserialize(document

                      35      xmlquery(

                      36          'copy $tmp := .

                      37           modify

                      38           (for $u in $upd/update

                      39              return replace value of node $tmp/Companies/Company[.=$u/@CMP_OLD] with $u/@CMP_NEW

                      40           )

                      41           return $tmp

                      42           '

                      43           passing

                      44              xmldata,

                      45              updates as "upd"

                      46           returning content)

                      47  as varchar2(150) indent) as updated_xml

                      48  from updatelist join inputdata on updatelist.pk = inputdata.pk ;

                      ERROR:

                      ORA-18126: XUDY0027 - Invalid target expression

                      • 8. Re: how to update XML node with PL/SQL
                        cormaco

                        Filter out the empty nodes before the group by

                         

                            where cmp_old is not null
                            group by pk 
                        • 9. Re: how to update XML node with PL/SQL
                          Big Pang

                          Thanks Cormaco for quick response !

                           

                          Furthermore, I find extra tip at https://stackoverflow.com/questions/31658471/replace-value-of-an-empty-node-with-0

                           

                          it use "where" to skip empty XML node inside Xquery update portion. Like this

                           

                          modify (

                                    for $i in ...

                                          where string-length($i) != 0

                                                 return replace value of node ....

                           

                          So I got ways to skip empty XML node before XQuery and during XQuery. A good day for me.

                          • 10. Re: how to update XML node with PL/SQL
                            cormaco

                            Here is another way to filter out empty nodes at the earliest stage in the XMLTABLE statement:

                             

                                FROM inputdata,XMLTABLE( 
                                    '/Companies/Company[./text()]' 

                             

                            This selects only the Company nodes that have a text node as a child.

                            • 11. Re: how to update XML node with PL/SQL
                              Big Pang

                              Yeah, it is another good one.