Do you need to check the validity of your XML documents without having an XML schema registered? This was a question I received from one of my customers. Did you know that Oracle Database includes a package named DBMS_XMLSCHEMA_UTIL, which allows for “on the fly” validation of XML documents? While it can be utilized in 23ai, it has also been backported from version 19.9.0.0 onwards.
In order to understand how it works, let’s demonstrate it with the following examples.
The PL/SQL package DBMS_XMLSCHEMA_UTIL includes the following two programs:
- the function DBMS_XMLSCHEMA_UTIL.CONFORMING ( doc IN XMLTYPE, sch IN XMLTYPE) RETURN NUMBER;
- and the procedure DBMS_XMLSCHEMA_UTIL.VALIDATE ( doc IN XMLTYPE, sch IN XMLTYPE);
The function and procedure accept an XML data instance (see parameter doc) and an XML schema instance (see parameter sch). The user does not have to register the schema.
I use Autonomous Database with database version 23.7 and the Cloud Shell to execute the code. In my first example, the procedure DBMS_XMLSCHEMA_UTIL.VALIDATE is run in the subsequent PL/SQL program:
SQL> set serveroutput on
SQL> set linesize window
DECLARE
l_xmlschema xmltype;
l_xmldoc XMLTYPE;
BEGIN
l_xmlschema := xmltype('<?xml version="1.0" encoding="UTF-8"?>
<xs:schema
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdb="http://xmlns.oracle.com/xdb"
elementFormDefault="qualified" attributeFormDefault="unqualified">
<xs:element name="ORDER" xdb:SQLName="ORDER" xdb:SQLType="ORDER_TYPE" xdb:defaultTable="ORDERTAB">
<xs:complexType>
<xs:sequence>
<xs:element name="CUSTOMER" type="CUSTOMERType" xdb:defaultTable="CUSTOMERTAB" xdb:SQLInline="false"/>
<xs:element name="FUNDS" type="FUNDSType" maxOccurs="unbounded" xdb:defaultTable="FUNDSTAB" xdb:SQLInline="false"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:complexType name="CUSTOMERType" xdb:SQLType="CUSTOMER_TYPE">
<xs:sequence>
<xs:element name="NAME" xdb:SQLName="NAME" xdb:SQLType="VARCHAR2"/>
<xs:element name="STREET" xdb:SQLName="STREET" xdb:SQLType="VARCHAR2"/>
<xs:element name="CITY" xdb:SQLName="CITY" xdb:SQLType="VARCHAR2"/>
<xs:element name="ACCOUNT" xdb:SQLName="ACCOUNT" xdb:SQLType="VARCHAR2"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="FUNDSType" xdb:SQLType="FUNDS_TYPE">
<xs:sequence>
<xs:element name="WKN" xdb:SQLName="WKN" xdb:SQLType="VARCHAR2"/>
<xs:element name="FUNDSNAME" xdb:SQLName="FUNDSNAME" xdb:SQLType="VARCHAR2"/>
<xs:element name="AMOUNT" xdb:SQLName="AMOUNT" xdb:SQLType="VARCHAR2"/>
</xs:sequence>
</xs:complexType>
</xs:schema>');
l_xmldoc := xmltype ('<?xml version="1.0" encoding="UTF-8"?>
<ORDER xmlns:xdb="http://xmlns.oracle.com/xdb"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://www.fundsinc.com/funds/order.xsd">
<CUSTOMER>
<NAME>Max Mustermann</NAME>
<STREET>Ringstr</STREET>
<CITY>Frankfurt</CITY>
<ACCOUNT>4711</ACCOUNT>
</CUSTOMER>
<FUNDS>
<WKN>471110</WKN>
<FUNDSNAME>Funds 1</FUNDSNAME>
<AMOUNT>10</AMOUNT>
</FUNDS>
</ORDER>');
DBMS_XMLSCHEMA_UTIL.VALIDATE(l_xmldoc, l_xmlschema);
dbms_output.put_line(' ');
dbms_output.put_line('----------------- '||'XML document conforms'||' ------------------');
exception
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('-------SQLERRM-------------');
DBMS_OUTPUT.put_line (SQLERRM);
DBMS_OUTPUT.put_line ('-------FORMAT_ERROR_STACK--');
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
DBMS_OUTPUT.put_line (' ');
end;
/
We used an XML document that does conform to the schema. Thus, the outcome is as follows:
----------------- XML document conforms ------------------ PL/SQL procedure successfully completed.
In the second example, we utilize the following XML document with slight modification: The FUNDSNAME tag is missing.
<?xml version="1.0" encoding="UTF-8"?> <ORDER xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www.fundsinc.com/funds/order.xsd"> <CUSTOMER> <NAME>Max Mustermann</NAME> <STREET>Ringstr</STREET> <CITY>Frankfurt</CITY> <ACCOUNT>4711</ACCOUNT> </CUSTOMER> <FUNDS> <WKN>471110</WKN> -- <FUNDSNAME>Funds 1</FUNDSNAME> <AMOUNT>10</AMOUNT> </FUNDS> </ORDER>');
Now we receive the following error message:
-------SQLERRM------------- ORA-31154: invalid XML document -------FORMAT_ERROR_STACK-- ORA-31154: invalid XML document ORA-19202: error in XML processing LSX-00201: contents of "FUNDS" should be elements only LSX-00213: only 0 occurrences of particle "sequence", minimum is 1 ORA-06512: at "SYS.DBMS_XMLSCHEMA_UTIL", line 7 ORA-06512: at "SYS.DBMS_XMLSCHEMA_UTIL", line 42 PL/SQL procedure successfully completed.
If you prefer using a function, execute the function CONFORMING. The function returns zero if the schema is legal and the document conforms to the schema; otherwise it returns an LSX error code from the schema validation.
The next example shows a conforming document result validation:
SQL> select DBMS_XMLSCHEMA_UTIL.CONFORMING(
XMLType('<Person/>'),
XMLType('<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Person" type="xs:string"/>
</xs:schema>')) "LSX CODE";
LSX CODE
----------
0
The next one is not conforming:
SQL> select DBMS_XMLSCHEMA_UTIL.CONFORMING(
XMLType('<B/>'),
XMLType('<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="A" type="xs:string"/>
</xs:schema>')) "LSX CODE" ;
LSX CODE
----------
21
You can find more information in PL/SQL Packages and Types Reference dbms_xmlschema_util.