6 Replies Latest reply: Sep 19, 2012 1:18 PM by odie_63 RSS

    Validating XML against XSD

    fiedlerm
      Hi,

      I'm in trouble validating a XML against a XSD Schema.

      I have the following XSD - Format:

      BEGIN
      DBMS_XMLSCHEMA.deleteSchema('http://localhost/public/xsd/imp.xsd',4);

      dbms_xmlschema.registerSchema(
      schemaURL => 'http://localhost/public/xsd/imp.xsd'
      , schemaDoc =>
      '<?xml version="1.0" encoding="windows-1252" ?>
      <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.example.org"
      targetNamespace="http://www.example.org" elementFormDefault="qualified">
      <xsd:element name="ImportService">
      <xsd:complexType>
      <xsd:sequence>
      <xsd:element name="PO" type="xsd:string"/>
      <xsd:element name="RO" maxOccurs="unbounded">
      <xsd:complexType>
      <xsd:sequence>
      <xsd:element name="Data" maxOccurs="unbounded">
      <xsd:complexType>
      <xsd:sequence>
      <xsd:element name="Field" maxOccurs="unbounded">
      <xsd:complexType>
      <xsd:sequence>
      <xsd:element name="Value" type="xsd:string"/>
      </xsd:sequence>
      <xsd:attribute name="Name" type="xsd:string"/>
      </xsd:complexType>
      </xsd:element>
      </xsd:sequence>
      </xsd:complexType>
      </xsd:element>
      </xsd:sequence>
      <xsd:attribute name="Name" type="xsd:string"/>
      </xsd:complexType>
      </xsd:element>
      </xsd:sequence>
      </xsd:complexType>
      </xsd:element>
      </xsd:schema>'
      , local => true
      , genTypes => false
      , genTables => false
      );
      END;

      and try to validate the following XML against the XSD:

      declare
      isValid integer;

      doc xmltype := xmltype('<?xml version="1.0" encoding="UTF-8" ?>
      <ImportService xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.example.org imp.xsd"
      xmlns="http://www.example.org">
      xsi:noNamespaceSchemaLocation="http://localhost/public/xsd/xxxxx.xsd">
      <PO>PO 1</PO>
      <RO Name="Ro 1">
      <Data>
      <Field Name="IVName">
      <Value>WR1</Value>
      </Field>
      <Field Name="TimeStamp">
      <Value>17/09/2012 09:27:39,263748</Value>
      </Field>
      <Field Name="Output">
      <Value>3776,993</Value>
      </Field>
      <Field Name="Udc">
      <Value>99,123</Value>
      </Field>
      <Field Name="Pdc">
      <Value>345,880</Value>
      </Field>
      <Field Name="W">
      <Value>456,127</Value>
      </Field>
      <Field Name="Temp">
      <Value>56,653</Value>
      </Field>
      </Data>
      <Data>
      <Field Name="IVName">
      <Value>WR2</Value>
      </Field>
      <Field Name="TimeStamp">
      <Value>17/09/2012 09:27:39,263748</Value>
      </Field>
      <Field Name="Output">
      <Value>1776,993</Value>
      </Field>
      <Field Name="Udc">
      <Value>29,123</Value>
      </Field>
      <Field Name="Pdc">
      <Value>245,880</Value>
      </Field>
      <Field Name="W">
      <Value>356,127</Value>
      </Field>
      <Field Name="Temp">
      <Value>45,653</Value>
      </Field>
      </Data>
      </RO>
      <RO Name="Roof 2">
      <Data>
      <Field Name="IVName">
      <Value>WR1</Value>
      </Field>
      <Field Name="TimeStamp">
      <Value>17/09/2012 09:27:39,263748</Value>
      </Field>
      <Field Name="Output">
      <Value>3776,993</Value>
      </Field>
      <Field Name="Udc">
      <Value>99,123</Value>
      </Field>
      <Field Name="Pdc">
      <Value>345,880</Value>
      </Field>
      <Field Name="W">
      <Value>456,127</Value>
      </Field>
      <Field Name="Temp">
      <Value>56,653</Value>
      </Field>
      </Data>
      <Data>
      <Field Name="IVName">
      <Value>WR2</Value>
      </Field>
      <Field Name="TimeStamp">
      <Value>17/09/2012 09:27:39,263748</Value>
      </Field>
      <Field Name="Output">
      <Value>1776,993</Value>
      </Field>
      <Field Name="Udc">
      <Value>29,123</Value>
      </Field>
      <Field Name="Pdc">
      <Value>245,880</Value>
      </Field>
      <Field Name="W">
      <Value>356,127</Value>
      </Field>
      <Field Name="Temp">
      <Value>45,653</Value>
      </Field>
      </Data>
      </RO>
      </ImportService>');

      begin
      doc := doc.deleteXML('/ROOT/@xsi:noNamespaceSchemaLocation','xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"');
      select xmlisvalid(doc, 'http://localhost/public/xsd/imp.xsd') into isValid from dual;
      dbms_output.put_line('isValid = '||to_char(isValid));
      end;

      But the document is invalid.
      Output: isValid = 0

      Can anybody help, please?

      Thanks
      Markus
        • 1. Re: Validating XML against XSD
          odie_63
          Hi,

          It would help if you could give a consistent and working test case.
          What you've posted so far is obviously not what you've tried :

          - The XML instance document is not wellformed
          - There's no ROOT element in the document

          And please give your exact database version (SELECT * FROM v$version).
          • 2. Re: Validating XML against XSD
            fiedlerm
            Hi,

            thanks for your fast reply.

            Here the extract of my database version (SELECT * FROM v$version):

            Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
            PL/SQL Release 11.2.0.3.0 - Production
            CORE     11.2.0.3.0     Production
            TNS for Linux: Version 11.2.0.3.0 - Production
            NLSRTL Version 11.2.0.3.0 - Production

            Here the valid XSD - File:

            <?xml version="1.0" encoding="UTF-8"?>
            <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.example.org" targetNamespace="http://www.example.org" elementFormDefault="qualified">
                 <xsd:element name="ImportService">
                      <xsd:complexType>
                           <xsd:sequence>
                                <xsd:element name="PO" type="xsd:string"/>
                                <xsd:element name="RO" maxOccurs="unbounded">
                                     <xsd:complexType>
                                          <xsd:sequence>
                                               <xsd:element name="Data" maxOccurs="unbounded">
                                                    <xsd:complexType>
                                                         <xsd:sequence>
                                                              <xsd:element name="Field" maxOccurs="unbounded">
                                                                   <xsd:complexType>
                                                                        <xsd:sequence>
                                                                             <xsd:element name="Value" type="xsd:string"/>
                                                                        </xsd:sequence>
                                                                        <xsd:attribute name="Name" type="xsd:string"/>
                                                                   </xsd:complexType>
                                                              </xsd:element>
                                                         </xsd:sequence>
                                                    </xsd:complexType>
                                               </xsd:element>
                                          </xsd:sequence>
                                          <xsd:attribute name="Name" type="xsd:string"/>
                                     </xsd:complexType>
                                </xsd:element>
                           </xsd:sequence>
                      </xsd:complexType>
                 </xsd:element>
            </xsd:schema>

            and here the valid XML - File:

            <?xml version="1.0" encoding="UTF-8"?>
            <ImportService xmlns="http://www.example.org" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.example.org imp.xsd">
                 <PO>PO 1</PO>
                 <RO Name="Ro 1">
                      <Data>
                           <Field Name="IVName">
                                <Value>WR1</Value>
                           </Field>
                           <Field Name="TimeStamp">
                                <Value>17/09/2012 09:27:39,263748</Value>
                           </Field>
                           <Field Name="Output">
                                <Value>3776,993</Value>
                           </Field>
                           <Field Name="Udc">
                                <Value>99,123</Value>
                           </Field>
                           <Field Name="Pdc">
                                <Value>345,880</Value>
                           </Field>
                           <Field Name="W">
                                <Value>456,127</Value>
                           </Field>
                           <Field Name="Temp">
                                <Value>56,653</Value>
                           </Field>
                      </Data>
                      <Data>
                           <Field Name="IVName">
                                <Value>WR2</Value>
                           </Field>
                           <Field Name="TimeStamp">
                                <Value>17/09/2012 09:27:39,263748</Value>
                           </Field>
                           <Field Name="Output">
                                <Value>1776,993</Value>
                           </Field>
                           <Field Name="Udc">
                                <Value>29,123</Value>
                           </Field>
                           <Field Name="Pdc">
                                <Value>245,880</Value>
                           </Field>
                           <Field Name="W">
                                <Value>356,127</Value>
                           </Field>
                           <Field Name="Temp">
                                <Value>45,653</Value>
                           </Field>
                      </Data>
                 </RO>
                 <RO Name="Ro 2">
                      <Data>
                           <Field Name="IVName">
                                <Value>WR1</Value>
                           </Field>
                           <Field Name="TimeStamp">
                                <Value>17/09/2012 09:27:39,263748</Value>
                           </Field>
                           <Field Name="Output">
                                <Value>3776,993</Value>
                           </Field>
                           <Field Name="Udc">
                                <Value>99,123</Value>
                           </Field>
                           <Field Name="Pdc">
                                <Value>345,880</Value>
                           </Field>
                           <Field Name="W">
                                <Value>456,127</Value>
                           </Field>
                           <Field Name="Temp">
                                <Value>56,653</Value>
                           </Field>
                      </Data>
                      <Data>
                           <Field Name="IVName">
                                <Value>WR2</Value>
                           </Field>
                           <Field Name="TimeStamp">
                                <Value>17/09/2012 09:27:39,263748</Value>
                           </Field>
                           <Field Name="Output">
                                <Value>1776,993</Value>
                           </Field>
                           <Field Name="Udc">
                                <Value>29,123</Value>
                           </Field>
                           <Field Name="Pdc">
                                <Value>245,880</Value>
                           </Field>
                           <Field Name="W">
                                <Value>356,127</Value>
                           </Field>
                           <Field Name="Temp">
                                <Value>45,653</Value>
                           </Field>
                      </Data>
                 </RO>
            </ImportService>


            Now I want to validate the XML against the XSD - Format via PL/SQL.

            Regards
            Markus
            • 3. Re: Validating XML against XSD
              odie_63
              I saved both the schema and the XML document as files (for convenience), then :
              SQL> BEGIN
                2  
                3    dbms_xmlschema.registerSchema(
                4      schemaURL => 'imp.xsd'
                5    , schemaDoc => xmltype(bfilename('TEST_DIR','imp.xsd'), nls_charset_id('AL32UTF8'))
                6    , local => true
                7    , genTypes => false
                8    , genTables => false
                9    , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
               10    );
               11  
               12  END;
               13  /
               
              PL/SQL procedure successfully completed
               
              SQL> 
              SQL> set serveroutput on
              SQL> 
              SQL> DECLARE
                2  
                3    doc      xmltype;
                4    isValid  pls_integer;
                5  
                6  BEGIN
                7  
                8    doc := xmltype(bfilename('TEST_DIR','sample_imp.xml'), nls_charset_id('AL32UTF8'));
                9  
               10    select xmlisvalid(doc) into isValid from dual;
               11    dbms_output.put_line('isValid = ' || isValid);
               12  
               13    --doc.schemaValidate();
               14  
               15  END;
               16  /
               
              isValid = 1
               
              PL/SQL procedure successfully completed
               
              seems to work nicely :)

              You may also use the schemaValidate() method which will give you a meaningful error message in case of validation failure.
              • 4. Re: Validating XML against XSD
                fiedlerm
                Hi,

                thanks. I'll try it and come back with the result.

                Regards
                Markus
                • 5. Re: Validating XML against XSD
                  fiedlerm
                  Hi,

                  good news - bad news.
                  Your solution works fine.

                  But in my envoronment I have no directory to validate the xml against the schema.

                  A PL/SQL Funktion / Procedure has to handle the XML als Parameter (CLOB).
                  The validation has to be done in this Function / Procedure.
                  So I have to implement the XSD within this Function / Procedure.

                  Any Ideas for this usecase?

                  Regards
                  Markus
                  • 6. Re: Validating XML against XSD
                    odie_63
                    But in my envoronment I have no directory to validate the xml against the schema.
                    Why would you need a directory?

                    As I said, in my example, I used external files only for my own convenience.

                    In your case, just use the CLOB parameter directly.
                    And you don't even need a custom function :
                    SELECT XMLIsValid(XMLType(:p_clob)) FROM dual;