Adding XML generated from SQL to existing schema-based XML may result in error ORA-30937 (Doc ID 351825.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.4 to 12.1.0.2 [Release 10.1 to 12.1]
Information in this document applies to any platform.

Symptoms

Procedure to create a schema-based XML document with dbms_xmldom fails with ORA-30937, as in the following example:

This procedure creates a schema-based xml document with dbms_xmldom. It works fine until you want to add some xml generated out of a SQL statement (using DBMS_XMLGEN):   

PROCEDURE PRC$IMJV2 (p_jaar varchar2, p_bedrijf varchar2) is
l_xml xmltype;
l_schema varchar2(50) := 'http://oracle.com:8080/public/myschema.xsd';
l_valid varchar2(3);
doc dbms_xmldom.domdocument;
docelem dbms_xmldom.domelement;
docnode dbms_xmldom.domnode;
ndoc dbms_xmldom.domdocument;
ndocelem dbms_xmldom.domelement;
ndocnode dbms_xmldom.domnode;
nelem dbms_xmldom.domelement;
nnode dbms_xmldom.domnode;
pnode dbms_xmldom.domnode;
ntext dbms_xmldom.domtext;
tnode dbms_xmldom.domnode;
QryCtx Dbms_XmlGen.ctxhandle;
result xmltype;
buf varchar2(2000);
--
Begin
l_xml := xmltype('<imjv:imjv
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" '
|| 'xmlns:imjv="http://oracle.com.myschema.oracle.com" '
|| 'xmlns="http://schema.oracle.com" '
|| 'xsi:schemaLocation="http://schema.oracle.com
http://oracle.com:8080/public/myschema"> '
|| '<meldingsjaar>' || p_jaar ||
'</meldingsjaar><cbbnummer>125</cbbnummer></imjv:imjv>')
.createschemabasedxml(l_schema);
doc := dbms_xmldom.newdomdocument(l_xml);
docelem := dbms_xmldom.getDocumentElement( doc );
docnode := dbms_xmldom.makenode(docelem);
nelem :=
dbms_xmldom.createelement(doc,'Lucht','http://schema.oracle.com);
nnode := dbms_xmldom.makenode(nelem);
pnode := dbms_xmldom.appendchild(docnode,nnode);
nelem :=
dbms_xmldom.createelement(doc,'activiteiten_typeA','http://schema.oracle.com');
nnode := dbms_xmldom.makenode(nelem);
pnode := dbms_xmldom.appendchild(pnode,nnode);
QryCtx := dbms_xmlgen.newcontext('SELECT  installatie, apparaat, beschrijving '
|| ' FROM Milieu.vw$activiteit_typea_2  '
|| ''' AND rownum < 2 and jaar = ''' || p_jaar || '''');
dbms_xmlgen.setrowsettag(QryCtx,NULL);
dbms_xmlgen.setrowtag(QryCtx,'activiteit');
Dbms_xmlgen.getxmltype(QryCtx, result);
ndoc := dbms_xmldom.newdomdocument(result.getclobval);
ndocelem := dbms_xmldom.getdocumentelement(ndoc);
nnode := dbms_xmldom.importnode(doc,dbms_xmldom.makenode(ndocelem),true);
pnode := dbms_xmldom.appendchild(pnode,nnode);
dbms_xmldom.freedocument(ndoc);
dbms_xmlgen.closecontext(QryCtx);
xmltype.schemavalidate(l_xml);
if l_xml.isschemavalidated() = 1 then
dbms_output.put_line('valid');
else dbms_output.put_line('invalid');
end if;
dbms_xmldom.writetofile(doc,'MILIEUXML/imjv.xml');
dbms_xmldom.writetobuffer(doc,buf);
for i in 0..length(buf) / 80 loop
dbms_output.put_line(substr(buf,1  + 80*i,80));
end loop;
dbms_xmldom.freedocument(doc);

This fails as follows:

ERROR at line 1:
ORA-30937: No schema definition for 'active' (namespace '##local') in
parent 'active_typeA'
ORA-06512: at "XDB.DBMS_XMLDOM", line 517
ORA-06512: at "XDB.DBMS_XMLDOM", line 538
ORA-06512: at "SR4962600.PRC$IMJV2", line 49
ORA-06512: at line 1

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms