Skip to Main Content
  • Questions
  • XML file removing namespaces in attributes

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Kalyana.

Asked: September 29, 2021 - 6:12 am UTC

Last updated: September 30, 2021 - 10:01 am UTC

Version: 11.2.0.2.0

Viewed 100+ times

You Asked

Hello Tom & Team,

Greetings to All of you!!!
My query is in continuation w.r.t to an earlier question asked in the forum.

"removing attribute from very large xml" date October 04, 2019 - 12:10 pm UTC.

In this you have mentioned how to remove namespaces in the root element.

My doubt is how to remove the namespaces in both the root and the child elements.

I am attaching the xml file here and the target environment is Oracle 11g.
------------------------------------------------------------------------------
<?xml version ="1.0"?>
<MTMROOT xmlns=" http://www.mtg.com/ILSENET/Interface" >
<MTMDATA>
<MTMFileupload xmlns:xsl=" http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd=" http://ww.w3.org/2001/XMLSchema" >
<StartDate>2021-05-28T01:06:51.8576073-04:00</StartDate>
<sid>2c08b76-bbe-9f32-936cbf958779</sid>
<DGroups>1200</DGroups>
<NumRecords>10</NumRecords>
<LineDetails>
<LineDetail> <UserAttr1>ABC</UserAttr1>
<UserAttr2>DEG</UserAttr2>
<UserAttr3>MIS</UserAttr3>
<UserAttr4>VRL</UserAttr4>
</LineDetail>
</LineDetails>
</MTMFileupload>
</MTMDATA>
</MTMROOT>

---------------------------------------------------------------------------------------------------------
Please do reach out to me in case of any queries.

Thanks to all of you in advance.

Warm Regards
Kalyana Chakravarthy

and Chris said...

You can use XMLTRANSFORM to apply an XSLT (Transformer) to remove the namespaces.

I'm no XML expert, so here's an example using the XSLT from https://roytuts.com/remove-namespace-from-xml-using-xslt/

with rws as (
  select xmltype ( '<?xml version ="1.0"?>
<MTMROOT xmlns="http://www.mtg.com/ILSENET/Interface" >
<MTMDATA>
<MTMFileupload xmlns:xsl="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://ww.w3.org/2001/XMLSchema" >
<StartDate>2021-05-28T01:06:51.8576073-04:00</StartDate>
<sid>2c08b76-bbe-9f32-936cbf958779</sid>
<DGroups>1200</DGroups>
<NumRecords>10</NumRecords>
<LineDetails>
<LineDetail> <UserAttr1>ABC</UserAttr1>
<UserAttr2>DEG</UserAttr2>
<UserAttr3>MIS</UserAttr3>
<UserAttr4>VRL</UserAttr4>
</LineDetail>
</LineDetails>
</MTMFileupload>
</MTMDATA>
</MTMROOT>' ) x from dual
)
  select xmltransform ( x, xmltype ( '<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:template match="node()">
  <xsl:copy>
   <xsl:apply-templates select="node() | @*" />
  </xsl:copy>
 </xsl:template>
 <xsl:template match="*">
  <xsl:element name="{local-name()}">
   <xsl:apply-templates select="node() | @*" />
  </xsl:element>
 </xsl:template>
 <xsl:template match="@*">
  <xsl:copy>
   <xsl:apply-templates select="node() | @*" />
  </xsl:copy>
 </xsl:template>
</xsl:stylesheet>' )
).getStringVal() removed_ns
  from   rws;

REMOVED_NS                                                                                                                                                                                                                                                                                                                                                                                                                                                  

<MTMROOT>
 <MTMDATA>
  <MTMFileupload>
   <StartDate>2021-05-28T01:06:51.8576073-04:00</StartDate>
   <sid>2c08b76-bbe-9f32-936cbf958779</sid>
   <DGroups>1200</DGroups>
   <NumRecords>10</NumRecords>
   <LineDetails>
    <LineDetail>
     <UserAttr1>ABC</UserAttr1>
     <UserAttr2>DEG</UserAttr2>
     <UserAttr3>MIS</UserAttr3>
     <UserAttr4>VRL</UserAttr4>
    </LineDetail>
   </LineDetails>
  </MTMFileupload>
 </MTMDATA>
</MTMROOT>

Rating

  (2 ratings)

Comments

Kalyana, September 29, 2021 - 4:29 pm UTC

Hi Chris,
Thank you for the answer.
However I have one doubt,
This answer shows a hard coded xml file.
We would like to use the same from sql table with many rows ,
each row having a different xml file.

create tablet xml_table(id number,
xml_data xmltype);
I am passing the required xml file into the table t as a parameter
from a directory, e.g 'XML_DIR'


INSERT INTO xml_table (id, xml_file)
VALUES (
1
, xmltype(
bfilename('XML_DIR', 'file1.xml')
, nls_charset_id('AL32UTF8') -- or whatever the original encoding is
)
);
/

As per our requirement, we will insert many rows into the above table
.Instead of hard coding as in the answer, we need to pick up each xml file and remove the namespace. e.g in a loop.
The entire process is ongoing as many xml files come external system and hence the namespaces will need to be removed.
Could you please suggest an approach?
Please reach out to me in case of any queries.
Thank you
Warm Regards
Chris Saxon
September 29, 2021 - 6:24 pm UTC

Sure - you can pass any XMLType column to XMLTransform.

Just select it like I've done passing your table and column instead of RWS and X.

If you're loading the values from files and want to strip the namespace in the process, you should be able to

Create an external table to read the XML files
Load the transformed files with a statement like:
insert into target ...
  select xmltransform ( xmlcol, '<xslt ... ' )
  from external_table

Kalyana Chakravarthy, September 30, 2021 - 7:39 am UTC

Hello Chris & Team,
Thanks a Ton !!!
The solution worked.

With warm Regards!!
Chris Saxon
September 30, 2021 - 10:01 am UTC

Great, you're welcome