Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

XQuery insert node with XMLTYPE

Andrey BorodinFeb 8 2021

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0
Why is this SQL not delete a node t[class=1]?

declare 
 vQuery clob;
 pioResultXML xmltype := xmltype('
 <Root>
  <tlist>
   <t><class>1</class></t>
   <t><class>2</class></t>
  </tlist>
 </Root>
 ');
begin   
  vQuery := ' copy $tmp := $src modify (insert node $AdditionalFinInfo as first into $tmp/Root													
, delete nodes $tmp/Root/tlist/t[class=1] ) return $tmp';
  SELECT XMLQuery(vQuery passing 
    pioResultXML as "src",
    xmltype('<NewNode/>') as "AdditionalFinInfo"
    RETURNING content)
   INTO pioResultXML
   FROM dual;    
dbms_output.put_line(pioResultXML.getclobval);  
end;

Result
<Root><NewNode/><tlist><t><class>1</class></t><t><class>2</class></t></tlist></Root>
If change order in xquery to

 delete nodes $tmp/Root/tlist/t[class=1] ) return $tmp'
, insert node $AdditionalFinInfo as first into $tmp/Root													

everything works well
Also deletting node like delete nodes $tmp/Root/tlist/t without filter work correctly.

Comments

Processing

Post Details

Added on Feb 8 2021
0 comments
1,295 views