CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
llepec
llepec

Reputation: 51

xmltype - delete parent node without child

Is this possible to delete parent node without their childs in xmltype variable?

For example:

declare
  v1 xmltype := xmltype('<root>
                           <parent1>
                             <child>1</child>
                             <child>2</child>
                           </parent1>
                           <parent2>
                             <child2>3</child2>
                           </parent2>
                         </root>');
begin
  -- some code...
  dbms_output.put_line(v1.getClobVal());
end;

My purpose is to get this on output:

<root>
  <child>1</child>
  <child>2</child>
  <parent2>
    <child2>3</child2>
  </parent2>
</root>

I have tried to use deleteXML function, but it remove child nodes too...

So, if anyone is able to help me with this, I will be grateful :)

Upvotes: 1

Views: 835

Answers (1)

ErikL
ErikL

Reputation: 2041

Removing nodes can be done by using DELETEXML, for example like this:

select DELETEXML(v1,'/root/parent1') into v1 from dual;

But deleting parent1 will also delete all child nodes of parent 1. If you want to preserve the child nodes of parent1, you first have to make a copy of those nodes before removing parent1. You can do it this way:

declare
  v1 xmltype := xmltype('<root>
                           <parent1>
                             <child>1</child>
                             <child>2</child>
                           </parent1>
                           <parent2>
                             <child2>3</child2>
                           </parent2>
                         </root>');
begin

  --Append all child nodes of parent1 as child nodes of root before the parent2 node
  select insertxmlbefore(v1, '/root/parent2',extract(v1,'/root/parent1/child::node()')) into v1 from dual;

  --remove parent 1
  select DELETEXML(v1,'/root/parent1') into v1 from dual;

  dbms_output.put_line(v1.getClobVal());

end;

This will give you:

<root>
    <child>1</child>
    <child>2</child>
    <parent2>
        <child2>3</child2>
    </parent2>
</root>

Hope that helps.

Upvotes: 1

Related Questions