Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Zilvinas.

Asked: March 30, 2017 - 4:09 pm UTC

Last updated: March 23, 2023 - 1:55 pm UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 1000+ times

You Asked

Hello,

I have a problem validating xml file.
The link where is all XSD files and xml file to validate is here:
https://www.vmi.lt/cms/documents/10162/6703244/SPECIFIKACIJA_MAI_55.zip/c86d3e1d-b081-47d6-a9a1-b021f6dcc85c

I've registered schema and checked in Oracle and got result 0 (Schema does not match XSD)
Then I used XMLSPY and Notepad++ plugin to check and result was that file is valid.
Only thing needed to do is to replace M55TypesSti_v0.1.xsd with M55TypesSti_v0.2.xsd

There is code I used with Oracle(data from files is to big to paste here, but is easy to paste directly from zip file from link above)
DECLARE
  l_Clob CLOB;
  l_Xml XMLTYPE := XMLTYPE(...); -- Copied file from /XML pavyzdžiai/MAI55_SLIK_XML_v01.xml
-- !!! M55TypesSti_v0.1.xsd inside this file was changed to M55TypesSti_v0.2.xsd
BEGIN
-- Because file file /MAI_55_LT_XSD-v0.2/M55TypesSti_v0.2.xsd is to big for VARCHAR2 datatype so I used
-- Dbms_Lob.Append(l_Clob, ...) But maybe will be easier for you to load file from disk...

  Dbms_XmlSchema.RegisterSchema('CommonTypesSti_v0.2.xsd', ...); -- Copied file body from /MAI_55_LT_XSD-v0.2/CommonTypesSti_v0.2.xsd
  Dbms_XmlSchema.RegisterSchema('IsoTypesSti_v0.2.xsd', ...); -- Copied file body from /MAI_55_LT_XSD-v0.2/IsoTypesSti_v0.2.xsd
  Dbms_XmlSchema.RegisterSchema('M55TypesSti_v0.2.xsd', l_Clob);
  Dbms_XmlSchema.RegisterSchema('CommonTypesSti_v0.2.xsd', ...); -- Copied file body from /MAI_55_LT_XSD-v0.2/CommonTypesSti_v0.2.xsd

  Dbms_Output.Put_Line(l_Xml.isSchemaValid('M55slik_v0.2')); -- returns 0
END;

and Chris said...

Your code doesn't show a registration for M55slik_v0.2. So there is no schema to validate against!

Your call to l_Xml.isSchemaValid should reference one of the xsds you registered in prior to this...

Rating

  (6 ratings)

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

Comments

My mistake

Zilvinas Vidmantas, April 03, 2017 - 7:56 am UTC

Sorry, I've made a mistake.
I registered that M55slik_v0.2.
Just when writing to you I've copied first line and forgot to change CommonTypesSti_v0.2.xsd to M55slik_v0.2.

I've never did XSD validation before. So here is great possibility that I'm doing something wrong.

Someone told me that I need first to create schema based XML before checking if it is valid. Something like this:
l_Xml := l_Xml.createSchemaBasedXML('M55slik_v0.2.xsd');
l_Xml.schemavalidate();

In this case I got error ORA-31000: Resource 'M55slik_v0.2.xsd' is not an XDB schema document

But what is strange that this error is raised not by createSchemaBasedXML('M55slik_v0.2.xsd');
But by schemavalidate();

If I change some letter in name M55slik_v0.2.xsd lets say to M55slik_v0.3.xsd then I get the same ORA-31000 but on l_Xml.createSchemaBasedXML('M55slik_v0.3.xsd');

So it seems that it should be registered("createSchemaBasedXML" did not throw an exception). But "schemavalidate" thinks otherwise and throws ORA-31000. Why?

What it is more strange that if I intentionally made xml not valid then Oracle throws exception about that. So validation is happening. But why I get ORA-31000 when xml seems to be valid?

In one example I found that dbms_xdb.createResource is called.
And after that

Dbms_XmlSchema.RegisterSchema('CommonTypesSti_v0.2.xsd', xdbURIType('/public/M55slik_v0.2.xsd').getClob());

Is this is different from just registering schema directly without dbms_xdb.createResource first?

Can you please tell all necessary steps that should be done to correctly validate xml. I've searched a lot on internet but still can't get it working right.

Thank you

Zilvinas Vidmantas, April 03, 2017 - 9:01 am UTC

I've tried your eaxpamle on https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9529444000346870090

And it works fine.

So it seems that only registerSchema and isSchemaValid is enough.
But cant get it working with my XSD and XML :(
Chris Saxon
April 03, 2017 - 9:19 am UTC

What exactly is your code and the error you're getting?

Share these and we'll see what we can do to help.

Zilvinas Vidmantas, April 03, 2017 - 9:33 am UTC

In my first review I posted link to all XSD and XML example. One XSD file is quite big to paste it here.

There is the link https://www.vmi.lt/cms/documents/10162/6703244/SPECIFIKACIJA_MAI_55.zip/c86d3e1d-b081-47d6-a9a1-b021f6dcc85c

This is a zip file. In folder MAI_55_LT_XSD-v0.2 there are XSD files.
I need to test only M55Slik_v0.2.xsd
But it uses CommonTypesSti_v0.2.xsd, IsoTypesSti_v0.2.xsd, M55TypesSti_v0.2.xsd. In these files there are only types. Element is in M55Slik_v0.2.xsd


And my code is as simple as DBMS_XMLSCHEMA.registerSchema for all these four XSD and then isSchemaValid('M55slik_v0.2').

XML file to be tested is in folder "XML pavyzdžiai" named MAI55_SLIK_XML_v0.1.xml.

One note! In xml file there is M55TypesSti_v0.1.xsd, but I assume it should be M55TypesSti_v0.2.xsd.

Oracle says is not valid.
If I try
y := x.createSchemaBasedXML('M55slik_v0.2.xsd');
y.schemavalidate();


Then I get ORA-31000
Chris Saxon
April 03, 2017 - 3:50 pm UTC

We've got your files.

But please: show us the exact code you run! Everything. In it's entirety.

Anyway. The schema name you pass to isSchemaValid has to match the name of the schema you registered exactly.

This includes the .xsd extension:

BEGIN
  Dbms_XmlSchema.RegisterSchema('M55slik_v0.2.xsd', '<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="a" type="xs:string"/>
</xs:schema>');
END;
/
DECLARE
  l_Xml XMLTYPE := XMLTYPE('<a></a>'); 
BEGIN
  Dbms_Output.Put_Line(l_Xml.isSchemaValid('M55slik_v0.2')); 
  Dbms_Output.Put_Line(l_Xml.isSchemaValid('M55slik_v0.2.xsd')); 
END;
/

0
1

Zilvinas Vidmantas, April 03, 2017 - 9:35 am UTC

The order of xsd files to register is as follows:
CommonTypesSti_v0.2.xsd
IsoTypesSti_v0.2.xsd
M55TypesSti_v0.2.xsd
M55Slik_v0.2.xsd

xml to xsd in oracle.

Rajeshwaran Jeyabal, March 08, 2023 - 10:37 am UTC

is there is any tool or API available in oracle to get XSD from XML?
Connor McDonald
March 09, 2023 - 4:15 am UTC

Not to my knowledge.

The closest we use to have was generating a schema from a type (which was useful if you were storing XML as obejct-relational) but that routine has been desupported on 21c, so I presume there was little demand for it.


xml to object-relation table.

Rajeshwaran Jeyabal, March 09, 2023 - 12:23 pm UTC

the below demo was from Oracle 21c EE (21.3) - Here is my XML

<?xml version="1.0"?>
<ROWSET>
  <DEPT>
    <DEPTNO>10</DEPTNO>
    <DNAME>ACCOUNTING</DNAME>
    <LOC>NEW YORK</LOC>
    <EMP_LIST>
      <EMP_ROW>
        <EMPNO>7782</EMPNO>
        <ENAME>CLARK</ENAME>
        <JOB>MANAGER</JOB>
        <MGR>7839</MGR>
        <HIREDATE>09-JUN-1981 00:00:00</HIREDATE>
        <SAL>2450</SAL>
      </EMP_ROW>
      <EMP_ROW>
        <EMPNO>7839</EMPNO>
        <ENAME>KING</ENAME>
        <JOB>PRESIDENT</JOB>
        <HIREDATE>17-NOV-1981 00:00:00</HIREDATE>
        <SAL>5000</SAL>
      </EMP_ROW>
      <EMP_ROW>
        <EMPNO>7934</EMPNO>
        <ENAME>MILLER</ENAME>
        <JOB>CLERK</JOB>
        <MGR>7782</MGR>
        <HIREDATE>23-JAN-1982 00:00:00</HIREDATE>
        <SAL>1300</SAL>
      </EMP_ROW>
    </EMP_LIST>
  </DEPT>
</ROWSET>


got XSD generated for it using https://www.liquid-technologies.com/online-xml-to-xsd-converter
then registered it to database like this
demo@PDB1> declare
  2     l_clob clob;
  3  begin
  4     l_clob := q'# <?xml version="1.0" encoding="utf-8"?>
  5  <!-- Created with Liquid Technologies Online Tools 1.0 (https://www.liquid-technologies.com) -->
  6  <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  7    <xs:element name="ROWSET">
  8      <xs:complexType>
  9        <xs:sequence>
 10          <xs:element name="DEPT">
 11            <xs:complexType>
 12              <xs:sequence>
 13                <xs:element name="DEPTNO" type="xs:unsignedByte" />
 14                <xs:element name="DNAME" type="xs:string" />
 15                <xs:element name="LOC" type="xs:string" />
 16                <xs:element name="EMP_LIST">
 17                  <xs:complexType>
 18                    <xs:sequence>
 19                      <xs:element maxOccurs="unbounded" name="EMP_ROW">
 20                        <xs:complexType>
 21                          <xs:sequence>
 22                            <xs:element name="EMPNO" type="xs:unsignedShort" />
 23                            <xs:element name="ENAME" type="xs:string" />
 24                            <xs:element name="JOB" type="xs:string" />
 25                            <xs:element minOccurs="0" name="MGR" type="xs:unsignedShort" />
 26                            <xs:element name="HIREDATE" type="xs:string" />
 27                            <xs:element name="SAL" type="xs:unsignedShort" />
 28                          </xs:sequence>
 29                        </xs:complexType>
 30                      </xs:element>
 31                    </xs:sequence>
 32                  </xs:complexType>
 33                </xs:element>
 34              </xs:sequence>
 35            </xs:complexType>
 36          </xs:element>
 37        </xs:sequence>
 38      </xs:complexType>
 39    </xs:element>
 40  </xs:schema> #';
 41
 42     dbms_xmlschema.registerSchema('http://www.oracle.com/mydemo.xsd' ,
 43                     schemaDoc => l_clob );
 44  end;
 45  /

PL/SQL procedure successfully completed.

demo@PDB1> select table_name,xmlschema,storage_type
  2  from user_xml_tables ;

TABLE_NAME           XMLSCHEMA                                STORAGE_TYPE
-------------------- ---------------------------------------- --------------------
ROWSET2806_TAB       http://www.oracle.com/mydemo.xsd         OBJECT-RELATIONAL


then tried to insert the above xml to the object relational table using dbms_xmlstore, but ended error like this

demo@PDB1> declare
  2     ctx DBMS_XMLSTORE.ctxType;
  3     l_xmltype xmltype;
  4     l_clob clob;
  5     n number;
  6  begin
  7     l_clob := '<?xml version="1.0"?>
  8  <ROWSET>
  9    <DEPT>
 10      <DEPTNO>10</DEPTNO>
 11      <DNAME>ACCOUNTING</DNAME>
 12      <LOC>NEW YORK</LOC>
 13      <EMP_LIST>
 14        <EMP_ROW>
 15          <EMPNO>7782</EMPNO>
 16          <ENAME>CLARK</ENAME>
 17          <JOB>MANAGER</JOB>
 18          <MGR>7839</MGR>
 19          <HIREDATE>09-JUN-1981 00:00:00</HIREDATE>
 20          <SAL>2450</SAL>
 21        </EMP_ROW>
 22        <EMP_ROW>
 23          <EMPNO>7839</EMPNO>
 24          <ENAME>KING</ENAME>
 25          <JOB>PRESIDENT</JOB>
 26          <HIREDATE>17-NOV-1981 00:00:00</HIREDATE>
 27          <SAL>5000</SAL>
 28        </EMP_ROW>
 29        <EMP_ROW>
 30          <EMPNO>7934</EMPNO>
 31          <ENAME>MILLER</ENAME>
 32          <JOB>CLERK</JOB>
 33          <MGR>7782</MGR>
 34          <HIREDATE>23-JAN-1982 00:00:00</HIREDATE>
 35          <SAL>1300</SAL>
 36        </EMP_ROW>
 37      </EMP_LIST>
 38    </DEPT>
 39  </ROWSET>' ;
 40     l_xmltype := xmltype( l_clob, schema=> 'http://www.oracle.com/mydemo.xsd');
 41      l_xmltype.schemavalidate;
 42
 43     ctx := DBMS_XMLSTORE.newContext('ROWSET2806_TAB'); -- Get saved context
 44      n := DBMS_XMLSTORE.insertXML(ctx, l_xmltype);
 45     dbms_output.put_line( ' n ==> '|| n );
 46      commit;
 47
 48  end;
 49  /
        declare
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00222: error received from SAX callback function
ORA-06512: at "SYS.DBMS_XMLSTORE", line 78
ORA-06512: at "SYS.DBMS_XMLSTORE", line 88
ORA-06512: at line 44


so is it not possible to persist the xml to object relational table using dbms_xmlstore API ? any workaround possible to this?

Chris Saxon
March 23, 2023 - 1:55 pm UTC

I only know a little XML, so can't really help here. That said, the docs for this specifically state

DBMS_XMLSTORE provides the ability to store XML data in relational tables.

not object-relational tables.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here