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

Posted on 2008-11-18
Last Modified: 2013-12-07
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!
Question by:clevertrevor74
    LVL 6

    Expert Comment

    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.

    Author Comment

    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.
    LVL 6

    Accepted Solution

    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

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now