HOWTO: Validate XML data

Sometimes the “how to validate XML” question arises again on the OTN XMLDB Forum. A quick start would be to read up on the XMLDB FAQ Forum questions were Mark Drake demonstrates XML validation via some good examples. Another resource would be to read the Oracle XMLDB Developers Guide chapter about “Transforming and Validating XMLType Data“.

People who just like me, try to answer for fun and/or as a mind game the questions on a Oracle OTN forum, and knowing that if you do not ask for specifics, that you will have to deal with “the merry go round exercise”: With every answer given, you discover that this wasn’t the solution which was hoped for. Sometimes I am up for this because I don’t want to be bitching about:

What database version are you using?
Give a test case that contains:
- an XML document
- an XML Schema
- the used statements (SQL/XMLQuery/etc)
- the wanted end result
etc…

Sometimes it is just fun, to see if you are able to predict what the solution will be in the end (although it isn’t effective – I know).

The following examples are based on such an XMLDB OTN question: “Loading a hierarchichal XML it into relational tables”. The first step asked for was how to validate XML…

Until now I have given 4 examples, which I thought are good food for a blog post, based on XMLIsValid or are “embedded” in how the XMLType table has been created.

XML data contained in XMLType
XML data contained in an XMLType Table(or Column), constrained by an XML Schema
XML data stored in the XDB Repository (WebDAV)
XML data stored on Disk (Outside the database)

And yeah there are even more combinations I can think off – like storing (not registering) an XML schema in the repository.

The basis for the examples are the ones given by Mark (see here for the XML Schema)

SQL>  select * from v$version;
 
BANNER
------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
 
5 rows selected.

XMLType Instance Document

XML data is given by an XMLType.

SQL> select schema_url from user_xml_schemas;
 
SCHEMA_URL
--------------------------------------------------
test
 
1 row selected.
 
SQL> WITH stuff AS
  2     (SELECT xmltype('
  3                        Hello World
  4                        A
  5                        ABCD
  6                        WXYZ
  7                        1
  8                        2
  9                        1
 10                        2
 11                        Goodbye
 12                      ')
 13      xmlcol
 14      FROM dual
 15     )
 16  SELECT XMLisValid(xmlcol,'test') as "VALID?"
 17  FROM   stuff
 18  ;
 
   VALID?
---------
        1
 
1 row selected.
 
SQL> WITH stuff AS
  2     (SELECT xmltype('
  3                        Hello World
  4                        A
  5                        ABCD
  6                        WXYZ
  7                        1
  8                        2
  9                        1
 10                        2
 11                        Goodbye
 12                      ')
 13      xmlcol
 14      FROM dual
 15     )
 16  SELECT decode(XMLisValid(xmlcol,'test'), 1, 'TRUE', 0, 'FALSE') 
 17  as "VALID?"
 18  FROM   stuff;
 
VALID?
------
TRUE
 
1 row selected.
 
SQL> WITH stuff AS
  2     (SELECT xmltype('
  3                        Hello World
  4                        Hello World
  5                        A
  6                        ABCD
  7                        WXYZ
  8                        1
  9                        2
 10                        1
 11                        2
 12                        Goodbye
 13                      ')
 14      xmlcol
 15      FROM dual
 16     )
 17  SELECT decode(XMLisValid(xmlcol,'test'), 1, 'TRUE', 0, 'FALSE') 
 18  as "VALID?"
 19  FROM   stuff;
 
VALID?
------
FALSE
 
1 row selected.

SQL>

XMLType Table / Column Data

XML data is stored in an XMLType table. This could have been an Binary XML table which also can be constrained by an XML Schema (or XML Schemata).

SQL> insert into ROOT_TABLE values (xmltype(
  2  '
  3     Hello World
  4     A
  5     ABCD
  6     WXYZ
  7     1
  8     2
  9     1
 10     2
 11     Goodbye
 12  '
 13  ))
 14  / 
 
1 row created.
 
SQL> insert into ROOT_TABLE values (xmltype(
  2  '
  3     Hello World
  4     Hello World
  5     A
  6     ABCD
  7     WXYZ
  8     1
  9     2
 10     1
 11     2
 12     Goodbye
 13  '
 14  ))
 15  / 
insert into ROOT_TABLE values (xmltype(
                               *
ERROR at line 1:
ORA-30937: No schema definition for 'Illegal' (namespace '##local') in parent '/Root'

SQL> select schema_url from user_xml_schemas;
 
SCHEMA_URL
--------------------------------------------------
test
 
1 row selected.
 
SQL> set long 10000000
SQL> select * from root_table;
 
SYS_NC_ROWINFO$
--------------------------------------------------

  Hello World
  A
  ABCD
  WXYZ
  1
  2
  1
  2
  Goodbye

  
1 row selected.
 
SQL> select XMLisValid(value(rt),'test') as "VALID?"
  2  from root_table rt
  3  ;
 
   VALID?
---------
        1
 
1 row selected.
 
SQL> -- This example is the same as given by Mark in the XMLDB Forum FAQ 
SQL>

XMLType data stored in the XDB Repository / WebDAV

XML data is stored as a file resource in the WebDAV XDB Foldering.


SQL> -- CREATE A XML FILE CALLED "Hierarchichal_XML.xml" as a FILE(aka Resource) in the XDB WebDAV foldering in the root dir "/"

SQL> DECLARE
  2   
  3     XMLdoc XMLType:=xmltype('
  4               Hello World
  5               Hello World
  6               A
  7               ABCD
  8               WXYZ
  9               1
 10               2
 11               1
 12               2
 13               Goodbye
 14            ');
 15  
 16  BEGIN
 17   
 18       if (DBMS_XDB.CREATERESOURCE('/Hierarchichal_XML.xml', XMLdoc))
 19       then null;
 20       else
 21          DBMS_OUTPUT.PUT_LINE('Resource Hierarchichal_XML.xml could NOT be created');
 22       end if;
 23       commit;
 24   
 25  END;
 26  / 
 
PL/SQL procedure successfully completed.

SQL> select xdbURIType ('/Hierarchichal_XML.xml').getClob() from dual;
 
XDBURITYPE('/HIERARCHICHAL_XML.XML').GETCLOB()
--------------------------------------------------------------------------------

  Hello World
  Hello World
  A
  ABCD
  WXYZ
  1
  2
  1
  2
  Goodbye

 
1 row selected.
 
SQL> select SCHEMA_URL, QUAL_SCHEMA_URL
  2  from user_xml_schemas
  3  where schema_url='test';
 
SCHEMA_URL
---------------------------
QUAL_SCHEMA_URL
---------------------------
test
http://xmlns.oracle.com/xdb/schemas/OTN/test
 
 
1 row selected.
 
SQL> SELECT decode(XMLisValid(xdbURIType ('/Hierarchichal_XML.xml').getXML(),'test')
  2               , 1, 'TRUE'
  3               , 0, 'FALSE') 
  4         as "VALID?"
  5  FROM   dual
  6  ;
 
VALID
-----
FALSE
 
1 row selected.

XMLType data Stored on Disk (outside the Database)

XML data is stored in on disk outside the database.

SQL> create directory OTNDIR as '/tmp';
 
Directory created.
 
SQL> -- CREATE XML FILE on DISK (/tmp/Hierarchichal_XML.xml')
 
SQL> declare
  2    doc  DBMS_XMLDOM.DOMDocument;
  3    xdata XMLType:=xmltype('
  4                                Hello World
  5                                Hello World
  6                                A
  7                                ABCD
  8                                WXYZ
  9                                1
 10                                2
 11                                1
 12                                2
 13                                Goodbye
 14                           ');
 15  begin
 16  
 17    doc := DBMS_XMLDOM.NewDOMDocument(xdata);
 18    DBMS_XMLDOM.WRITETOFILE(doc, 'OTNDIR/Hierarchichal_XML.xml');
 19  
 20  end;
 21  /           
 
PL/SQL procedure successfully completed.
 
SQL> -- SELECT FROM THE XML FILE on DISK (/tmp/Hierarchichal_XML.xml')
 
SQL> SELECT extract((XMLTYPE(bfilename('OTNDIR','Hierarchichal_XML.xml'),NLS_CHARSET_ID('AL32UTF8'))),'*') AS "XDATA"
  2  FROM dual;
 
XDATA
--------------------------------------------------------------------------------
Hello WorldHello WorldAABCDWXYZ1212Goodbye
 
 
1 row selected.
 
SQL> SELECT decode(XMLisValid(XMLTYPE(bfilename('OTNDIR','Hierarchichal_XML.xml'),NLS_CHARSET_ID('AL32UTF8')),'test')
  2               , 1, 'TRUE'
  3               , 0, 'FALSE')
  4         as "VALID?"
  5  FROM   dual
  6  ;
 
VALID?
------
FALSE
 
1 row selected.

HTH

😉

One Comment

Marco Gralike

September 7

== UPDATE ==

As this post is of long long ago, I have learned that probably the best way is NOT to use XMLisValid but when manually checking if your content is valid against the schema is to use schemaValidate()

declare
  cursor getDocuments
  is
  select rownum, xmltype(somestuff) as OBJECT_VALUE
  from   TableWithXML;
begin
  for d in getDocuments() loop
    BEGIN
      d.OBJECT_VALUE.schemaValidate();
    EXCEPTION WHEN OTHERS
    	then
    	dbms_output.put_line (d.rownum||' = INVALID => '||sqlerrm);
    END;
  end loop;
end;
/

or if there is no reference or an invalid reference or you need to specify a specific reference to an XML Schema add the createSchemaBasedXML function.

declare
  cursor getDocuments
  is
  select rownum, twx.xml_columnname.createSchemaBasedXML('my_XML_Schema.xsd') 
    as OBJECT_VALUE
  from   TableWithXML twx
begin
  for d in getDocuments() loop
    BEGIN
      d.OBJECT_VALUE.schemaValidate();
    EXCEPTION WHEN OTHERS
      then
      dbms_output.put_line (d.rownum||' = INVALID => '||sqlerrm);
    END;
  end loop;
end;
/

Comments are closed.