Forum Stats

  • 3,749,903 Users
  • 2,250,074 Discussions
  • 7,866,685 Comments

Discussions

how to update XML node with PL/SQL

Big Pang
Big Pang Member Posts: 16
edited Oct 18, 2018 9:07AM in General XML

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.

mNem

Best Answer

  • cormaco
    cormaco Member Posts: 1,642 Bronze Crown
    edited Oct 11, 2018 4:03PM Accepted Answer

    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 updatesfrom 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>
    mNem
«1

Answers

  • cormaco
    cormaco Member Posts: 1,642 Bronze Crown
    edited Oct 11, 2018 4:03PM Accepted Answer

    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 updatesfrom 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>
    mNem
  • Big Pang
    Big Pang Member Posts: 16
    edited Oct 11, 2018 11:45PM

    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!

  • cormaco
    cormaco Member Posts: 1,642 Bronze Crown
    edited Oct 15, 2018 10:57AM
    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 allselect 2,xmltype('<Companies>    <Company>Nokia</Company>    <Company>Motorola</Company>    <Company>Honor</Company></Companies>') from dual union allselect 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>
  • Big Pang
    Big Pang Member Posts: 16
    edited Oct 16, 2018 11:11AM

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

  • Big Pang
    Big Pang Member Posts: 16
    edited Oct 17, 2018 9:52AM

    It worked as expected.

  • cormaco
    cormaco Member Posts: 1,642 Bronze Crown
    edited Oct 17, 2018 1:34PM

    That's good to hear.

  • Big Pang
    Big Pang Member Posts: 16
    edited Oct 17, 2018 2:44PM

    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

  • cormaco
    cormaco Member Posts: 1,642 Bronze Crown
    edited Oct 17, 2018 3:38PM

    Filter out the empty nodes before the group by

        where cmp_old is not null
        group by pk 
  • Big Pang
    Big Pang Member Posts: 16
    edited Oct 17, 2018 4:16PM

    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.

  • cormaco
    cormaco Member Posts: 1,642 Bronze Crown
    edited Oct 18, 2018 1:45AM

    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.