Oracle sql remove specific tag from string

Question

I want a function to pass a text (varchar2) and a tag name and return the text with the tag and their child tags removed Keep only

I don't know how levels of childs there are under tag.

Input :

set serverouput on
declare
v_input_str  varchar(4000) := '        <parent1>
            <Message>12ab</Message>
            <CreationDate>02/08/2015</CreationDate>
            <Total>500.45</Total>
            <Collect>
                <Name>Peter</Name>
            </Collect>
        </parent1>';
begin
 dbms_output.put_line(v_input_str);

end;
/


output :
'        <parent1>
            <Message>12ab</Message>
            <CreationDate>02/08/2015</CreationDate>
            <Total>500.45</Total>
            <Collect/>
        </parent1>'

Thanks in advance

Gurwinder Singh · Accepted Answer · 2017-05-11 18:26:54Z

2

You can use deleteXML for this.

Assuming your parameter tag is stored in tag variable, do:

declare
    v_input_str  clob := '        <parent1>
            <Message>12ab</Message>
            <CreationDate>02/08/2015</CreationDate>
            <Total>500.45</Total>
            <Collect>
                <Name>Peter</Name>
            </Collect>
        </parent1>';
    tag varchar2(20) := 'Collect';
begin
    select deleteXML(xmltype(v_input_str), '//'||tag||'/*').getClobVal() into v_input_str from dual;
    dbms_output.put_line(v_input_str);
end;

Output:

<parent1><Message>12ab</Message><CreationDate>02/08/2015</CreationDate><Total>500.45</Total><Collect/></parent1>

answered May 11, 2017 at 18:26

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

prokopis Over a year ago

Thanks for your answer. It works for me . But alignment is not as the initial.

Gurwinder Singh Over a year ago

Why does alignment matter?

Collectives™ on Stack Overflow

Oracle sql remove specific tag from string

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related