CODEWITHSUNDEEP
xmloracle-databaseplsqlxmltype
Jandrejc
Jandrejc

Reputation: 499

Deleting xml node in PL/SQL

How can I remove a node from xmltype using PL/SQL? Example:

<people>
   <person>
      <personNO>1</personNO>
   </person>
   <person>
      <personNO>2</personNO>
   </person>
</people>

I try to remove whole node person if /people/person/personNO is 1. My xml document is in the l_xml xmltype variable. So far I've got:

SELECT deletexml(l_xml, '/people/person[personNO="1"]')
INTO l_xml
FROM dual;

It doesn't seem to work at all. Can you please suggest any solution?

Upvotes: 1

Views: 6218

Answers (1)

DazzaL
DazzaL

Reputation: 21993

define "doesn't work at all" as it will work just fine:

(tested on 10.2.0.4 + 11.2.0.3)

SQL> declare
  2    l_xml xmltype;
  3  begin
  4
  5    l_xml := xmltype.createXML('<people>
  6     <person>
  7        <personNO>1</personNO>
  8     </person>
  9     <person>
 10        <personNO>2</personNO>
 11     </person>
 12  </people>');
 13
 14    select deletexml(l_xml, '/people/person[personNO="1"]')
 15      into l_xml
 16      from dual;
 17
 18    dbms_output.put_line(l_xml.getstringval());
 19  end;
 20  /
<people><person><personNO>2</personNO></person></people>

PL/SQL procedure successfully completed.

so the whole person node for person no = 1 was deleted. if you intended to wipe out every node if personNO 1 existed in any of them, then you want to set the xpath to /people/person[../person/personNO="1"]

Upvotes: 4

Related Questions