Saturday, 5 January 2013

Validate XML in Oracle against an XSD using DBMS_XMLSCHEMA and XMLTYPE.SCHEMAVALIDATE

The xml below

<userdetails>
   <username>Fred</username>
   <password>pass123</password>
</userdetails>
can be represented by the following XSD

<?xml version="1.0" encoding="UTF-8"?> 
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">   
   <xs:element name="userdetails">     
      <xs:complexType>       
         <xs:sequence>             
            <xs:element name="username" type="xs:string"/>         
            <xs:element name="password"  type="xs:string"/> 
         </xs:sequence>     
      </xs:complexType>   
   </xs:element> 
</xs:schema>
The xsd can be registered with the oracle database by making a call to dbms_xmlschema.registerSchema.
In the example below we have named this xsd as xsd1.xsd

BEGIN    

   dbms_xmlschema.registerSchema(schemaURL   => 'xsd1.xsd', 
                                 schemaDoc   => '<?xml version="1.0" encoding="UTF-8"?> 
                                                 <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">   
                                                 <xs:element name="userdetails">     
                                                 <xs:complexType>       
                                                 <xs:sequence>             
                                                 <xs:element name="username" type="xs:string"/>         
                                                 <xs:element name="password"  type="xs:string"/> 
                                                 </xs:sequence>     
                                                 </xs:complexType>   
                                                 </xs:element> 
                                                 </xs:schema>'); 
END; 
/ 
The registered schema can be viewed in the database by accessing the data dictionary all_xml_schemas

SELECT schema 
  FROM all_xml_schemas 
 WHERE schema_url = 'xsd1.xsd'
/

SCHEMA
----------------------------------------------------------------------------------------------------
<?xml version="1.0" encoding="WINDOWS-1252"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:oraxdb="http://xmlns.oracle.com/xdb" oraxdb:flags="295" oraxdb:schemaURL="xsd1.xsd" oraxdb:schemaOwner="MYSCHEMA" oraxdb:numProps="3">
   <xs:element name="userdetails" oraxdb:propNumber="2515" oraxdb:global="true" oraxdb:SQLName="userdetails" 
  oraxdb:SQLType="userdetails193_T" oraxdb:SQLSchema="MYSCHEMA" oraxdb:memType="258" oraxdb:defaultTable="userdetails194_TAB" oraxdb:defaultTableSchema="MYSCHEMA">
      <xs:complexType oraxdb:SQLType="userdetails193_T" oraxdb:SQLSchema="MYSCHEMA">
         <xs:sequence>
            <xs:element name="username" type="xs:string" oraxdb:propNumber="2513" oraxdb:global="false"
oraxdb:SQLName="username" oraxdb:SQLType="VARCHAR2" oraxdb:memType="1" oraxdb:SQLInline="true" oraxdb:MemInline="true" oraxdb:JavaInline="true"/>
            <xs:element name="password" type="xs:string" oraxdb:propNumber="2514" oraxdb:global="false"
oraxdb:SQLName="password" oraxdb:SQLType="VARCHAR2" oraxdb:memType="1" oraxdb:SQLInline="true" oraxdb:MemInline="true" oraxdb:JavaInline="true"/>
         </xs:sequence>
      </xs:complexType>
   </xs:element>
</xs:schema>
To validate the xml shown below

<userdetails>
   <username>Fred</username>
   <password>pass123</password>
</userdetails>
we need to add a reference to the xsd we registered earlier and called xsd1.xsd. To the outer node add the reference xsi:noNamespaceSchemaLocation="xsd1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

<userdetails xsi:noNamespaceSchemaLocation="xsd1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

We can then make a call to XMLTYPE.schemaValidate to validate the xml against the schema
DECLARE

l_xml         XMLTYPE ;

BEGIN    

   l_xml := XMLTYPE('<userdetails xsi:noNamespaceSchemaLocation="xsd1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                       <username>Fred</username>
                       <password>pass123</password>
                     </userdetails>');     
                     
   XMLTYPE.schemaValidate(l_xml); 

END; 
/


PL/SQL procedure successfully completed.
The successful execution of the pl/sql indicates the xml was valid. Adding an element that is not defined in the xsd (element newelement) will fail schema validation resulting in an exception being raised

DECLARE

l_xml         XMLTYPE ;

BEGIN    

   l_xml := XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
                     <userdetails xsi:noNamespaceSchemaLocation="xsd1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                       <username>Fred</username>
                       <password>pass123</password>
                       <newelement>dummy value</newelement>
                     </userdetails>');     
                     
   XMLTYPE.schemaValidate(l_xml); 

END; 
/

DECLARE
*
ERROR at line 1:
ORA-30937: No schema definition for 'newelement' (namespace '##local') in parent '/userdetails'
ORA-06512: at "SYS.XMLTYPE", line 345
ORA-06512: at line 14
If we don't have an XSD but already have a type representation in the database Oracle allows us to generate an XSD based upon that type

Our original XML

<userdetails>
   <username>Fred</username>
   <password>pass123</password>
</userdetails>
can be represented in the database by the following type

CREATE TYPE user_details_typ AS OBJECT
(
 "username"     VARCHAR2(20) ,
 "password"     VARCHAR2(30)
)
/

Type created.
Note, the attributes username and password have been enclosed in double quotes to preserve their case. To generate the XSD a call to dbms_xmlschema.generateschema is required.

SELECT DBMS_XMLSCHEMA.generateschema('MYSCHEMA', 'USER_DETAILS_TYP', 'userdetails') FROM DUAL ;

This will result in the following XSD being created
<?xml version="1.0"?>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:xdb="http://xmlns.oracle.com/xdb" 
xsi:schemaLocation="http://xmlns.oracle.com/xdb http://xmlns.oracle.com/xdb/XDBSchema.xsd">                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
 <xsd:element name="userdetails" type="USER_DETAILS_TYPType" xdb:SQLType="USER_DETAILS_TYP" xdb:SQLSchema="MYSCHEMA"/>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
 <xsd:complexType name="USER_DETAILS_TYPType" xdb:SQLType="USER_DETAILS_TYP" xdb:SQLSchema="MYSCHEMA" xdb:maintainDOM="false">                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  <xsd:sequence>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
   <xsd:element name="username" xdb:SQLName="username" xdb:SQLType="VARCHAR2">                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
    <xsd:simpleType>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
     <xsd:restriction base="xsd:string">                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
      <xsd:maxLength value="20"/>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
     </xsd:restriction>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
    </xsd:simpleType>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
   </xsd:element>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
   <xsd:element name="password" xdb:SQLName="password" xdb:SQLType="VARCHAR2">                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
    <xsd:simpleType>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
     <xsd:restriction base="xsd:string">                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
      <xsd:maxLength value="30"/>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
     </xsd:restriction>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
    </xsd:simpleType>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
   </xsd:element>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  </xsd:sequence>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
 </xsd:complexType>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
</xsd:schema> 
 
The parameters passed into dbms_xmlschema.generateschema were:
  • 'MYSCHEMA' - the name of the schema the object (user_details_typ) resides
  • 'USER_DETAILS_TYP' - the name of the object
  • 'userdetails' - the name of the top node
An issue with creating types where the attributes are enclosed in double quotes such as

CREATE TYPE user_details_typ AS OBJECT
(
 "username"     VARCHAR2(20) ,
 "password"     VARCHAR2(30)
)
/

is that in order to reference the type in pl/sql the attributes have to be enclosed in double quotes to preserve their case

DECLARE

l_user_details     user_details_typ ;

BEGIN

   l_user_details := user_details_typ (NULL, NULL) ;
   l_user_details."username" := 'Fred' ;

END ;
/

PL/SQL procedure successfully completed.

Failing to use double quotes will result in an error

DECLARE

l_user_details     user_details_typ ;

BEGIN

   l_user_details := user_details_typ (NULL, NULL) ;
   l_user_details.username := 'Fred' ;

END ;
/

ERROR at line 8:
ORA-06550: line 8, column 19:
PLS-00302: component 'USERNAME' must be declared
ORA-06550: line 8, column 4:
PL/SQL: Statement ignored

Alternatively the object could be created without double quotes and the resulting xml could be tranformed using a style sheet.
The code below transforms xml with uppercase attribute names to xml with lower case attribute names using the xml method transform.

DECLARE

l_xml              XMLTYPE ;
l_xslt             XMLTYPE ;
l_transformed_xml  XMLTYPE ;

BEGIN 

   -- the original xml
   l_xml := XMLTYPE ('<USERDETAILS>
                        <USERNAME>Fred</USERNAME>
                        <PASSWORD>pass123</PASSWORD>
                     </USERDETAILS>') ;
                     
   dbms_output.put_line ('Original xml' || CHR(10)||'---------------------------') ;                  
   dbms_output.put_line (l_xml.getclobval) ;   
   
   
   -- stylesheet to transform all the element names to lower case
   
   l_xslt := XMLTYPE ('<?xml version="1.0" encoding="UTF-8"?>
                        <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
                        <xsl:output method="xml" encoding="UTF-8" version="1.0" />  
                          <xsl:template match="*">
                            <xsl:variable name="vElement"
                              select="translate(name(), ''ABCDEFGHIJKLMNOPQRSTUVWXYZ'',''abcdefghijklmnopqrstuvwxyz'')" />
                            <xsl:element name="{$vElement}">
                              <xsl:apply-templates />
                            </xsl:element>
                          </xsl:template>
                        </xsl:stylesheet>') ;      

   -- apply the xslt to the original xml
   
   l_transformed_xml := l_xml.transform (l_xslt) ;          
   
   dbms_output.put_line ('Transformed xml'|| CHR(10)||'---------------------------') ;      
   dbms_output.put_line (l_transformed_xml.getclobval) ;


END ;
/

Original xml
---------------------------
<USERDETAILS>
                        <USERNAME>Fred</USERNAME>

<PASSWORD>pass123</PASSWORD>
                     </USERDETAILS>
Transformed xml
---------------------------
<userdetails>
<username>Fred</username>
<password>pass123</password>
</userdetails>


PL/SQL procedure successfully completed.

2 comments:

  1. Hi ,

    We have some questions on this.

    we have 1,00,000 records like below.



    Fred
    pass123
    </userdetails

    We can not add the 1,00,000 records in the oracle xml type function. so we have xml file for 1,00,000 recrods. How to validate these 1,00,000 records with respect XSD.


    Please let us know how to validate.

    ReplyDelete
  2. https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9525415800346730520

    ReplyDelete