Skip to Main Content
  • Questions
  • removing attribute from very large xml

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, ekta.

Asked: October 04, 2019 - 12:10 pm UTC

Last updated: September 24, 2021 - 4:07 pm UTC

Version: 3.1

Viewed 1000+ times

You Asked

I have a xml of size 52kb and need to remove namespaces from xml,
below is the code im using which is working fine when xml was of 3-4 kb but when size increased
below is the error thrown is :
is 19011. 00000 - "Character string buffer too small"
*Cause: The string result asked for is too big to return back
*Action: Get the result as a lob instead

PROCEDURE remove_attributes
IS
V_DOM_DOC dbms_xmldom.DOMDocument;
V_ROOT_ELIMENT DBMS_XMLDOM.DOMELEMENT;
V_XMLTYPE XMLTYPE;
BEGIN
SELECT xml_col INTO V_XMLTYPE FROM table1;
V_DOM_DOC := dbms_xmldom.newDOMDocument(V_XMLTYPE);
V_ROOT_ELIMENT := DBMS_XMLDOM.GETDOCUMENTELEMENT(V_DOM_DOC);
-- Remove exact namespace.
dbms_xmldom.removeAttribute( V_ROOT_ELIMENT, 'xmlns' );

V_XMLTYPE := DBMS_XMLDOM.GETXMLTYPE(V_DOM_DOC);

UPDATE table1 SET XML_COL_UPDT = V_XMLTYPE;

update table1
set XML_COL_UPDT= replace(XML_COL_UPDT, 'rlc:',null);

DBMS_OUTPUT.PUT_LINE ('XMLNS ATTRIBUTE REMOVED');

END remove_attributes;

and Connor said...

Nothing to do with XML here, it is your use of "replace"

SQL> create table t (  xml_col xmltype, xml_col_updt xmltype);

Table created.

SQL>
SQL> set serverout on
SQL> declare
  2    x clob;
  3  begin
  4    x := q'[<?xml version = '1.0' encoding = 'UTF-8'?><ms:datapacket xmlns:ms=" http://www.abc.com/xyz/ms" xmlns:html=" http://www.w3.org/1999/xhtml" >]';
  5
  6    for i in 1 .. 1000 loop
  7      x := x || '<ms:data><ms:nonmultirow><ms:owner>Smith</ms:owner><ms:created_by/></ms:nonmultirow></ms:data>';
  8    end loop;
  9    x := x || '</ms:datapacket>';
 10    dbms_output.put_line(length(x));
 11    insert into t values (xmltype(x),null);
 12    commit;
 13  end;
 14  /
94155

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> declare
  2    v_dom_doc dbms_xmldom.domdocument;
  3    v_root_eliment dbms_xmldom.domelement;
  4    v_xmltype xmltype;
  5  begin
  6    select xml_col into v_xmltype from t;
  7    v_dom_doc := dbms_xmldom.newdomdocument(v_xmltype);
  8    v_root_eliment := dbms_xmldom.getdocumentelement(v_dom_doc);
  9
 10    dbms_xmldom.removeAttribute( v_root_eliment, 'xmlns' );
 11
 12    v_xmltype := dbms_xmldom.getxmltype(v_dom_doc);
 13
 14    update t set xml_col_updt = v_xmltype;
 15
 16  --  update t
 17  --  set xml_col_updt= replace(xml_col_updt, 'rlc:',null);
 18
 19  end;
 20  /

PL/SQL procedure successfully completed.



You could use the clob instead

SQL> declare
  2    v_dom_doc dbms_xmldom.domdocument;
  3    v_root_eliment dbms_xmldom.domelement;
  4    v_xmltype xmltype;
  5    c clob;
  6  begin
  7    select xml_col into v_xmltype from t;
  8    v_dom_doc := dbms_xmldom.newdomdocument(v_xmltype);
  9    v_root_eliment := dbms_xmldom.getdocumentelement(v_dom_doc);
 10
 11    dbms_xmldom.removeAttribute( v_root_eliment, 'xmlns' );
 12
 13    v_xmltype := dbms_xmldom.getxmltype(v_dom_doc);
 14
 15    update t set xml_col_updt = v_xmltype;
 16
 17    c := replace(v_xmltype.getclobval(), 'rlc:',null);
 18    update t
 19    set xml_col_updt= xmltype(c);
 20
 21  end;
 22  /

PL/SQL procedure successfully completed.



Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Kalyana, September 24, 2021 - 10:17 am UTC

Hi,
If the namespace is below the root, for.e.g you have in more than one place below the root, is it possible to use the above sample code?
If not , can we know the steps?
Thanks in advance,

Regards
Kalyanaa
Chris Saxon
September 24, 2021 - 4:07 pm UTC

You can have multiple namepsaces - what exactly is it you're trying to do? Give us an example!

removing namespaces from large xml

Kalyana, September 25, 2021 - 7:15 am UTC

Hi,
I am attaching the xml file which has multiple namespaces.
There are namespaces even below the root element.
Could you suggest a way to remove all the namespaces including the root element and below.

Regards
Kalyana
----------------------------------------------------------------------------
<?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>
--------------------------------------------------------------------

removing namespaces from large xml

Kalyana, September 27, 2021 - 1:40 pm UTC

Hi Team,
Is the given info sufficient?
Do you require more info?
The above operation needs to performed on Oracle 11g database.

Regards
Kalyana

Kalyana, September 28, 2021 - 1:39 pm UTC

Hello Team,
Greetings!!!
I have posted the required xml file.
Is the information sufficient?
I am not able to ask questions through the main page.
Hence I am writing here.
Requesting an update for the query.
Regards
Kalyana

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.