[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 921
  • Last Modified:

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="http://www.w3.org/2001/XMLSchema-instance"
        <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!
  • 2
1 Solution
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.
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now