Forum Stats

  • 3,837,615 Users
  • 2,262,274 Discussions
  • 7,900,331 Comments

Discussions

XQuery insert node with XMLTYPE

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.

Tagged: