CODEWITHSUNDEEP
sqloracle-databaseplsql
prokopis
prokopis

Reputation: 133

Oracle sql remove specific tag from string

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

Upvotes: 1

Views: 233

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39497

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>

Upvotes: 2

Related Questions