How do i remove the attribute "noNamespaceSchemaLocation" from xml file in oracle xml database?

I have stored on oracle in a specific resource folder some xml files. For example:

    <root xmlns:xsi=""
        <Element Reference="1" Name="A">
             <SubElement Name="A1" Reference="11"/>
        </Element > 

Now i want to copy them to another resource folder:

    SELECT '/public/DestFolder/' || path(1), RES, LINK, NULL FROM PATH_VIEW
    WHERE under_path(RES, '/public/SourceFolder', 1) = 1
    ORDER BY depth(1);

No problem.
But how do i remove (or change) the root-attribute  xsi:noNamespaceSchemaLocation="schema.xsd"? Is there any possibility to use XSLT inside PL/SQL stored procedure?
OK, i can download the files, change them and upload them again. But this is not my intention.

Thank you in advance!
Who is Participating?
Ritesh_GargConnect With a Mentor Commented:
With replace command, you do not need.
E.g. Select replace(l_char, xsi:noNamespaceSchemaLocation="schema.xsd")  from dual;

To find position of a string in another, use INSTR function.  E.g.

Select  substr(l_char, 1, instr(l_char, 'xsi:noNamespaceSchemaLocation="schema.xsd"') - 1)
         ||  substr(l_char, instr(l_char, 'xsi:noNamespaceSchemaLocation="schema.xsd"') + 1)
from dual
Are you generating these XML Files from PL/SQL?
If so, and if you are using CLOB datatype, use Replace command.

Else another option may be to use a small UNIX shell script to do this cleanup.  You may schedule it in chrontab.
clevertrevor74Author Commented:
Thank you for your answer.
I´m using XMLType. Should be no problem to handle Replace command with this type (convert to varchar2).

Is there any function to detect positions of substrings in a string (varchar2)? Because the position of the substring to remove is not known and must be searched for.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.