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.

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 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>

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 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>

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!

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 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

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

Big Pang

It worked as expected.

cormaco

That's good to hear.

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

cormaco

Filter out the empty nodes before the group by

	where cmp_old is not null
	group by pk

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.

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.