2 Replies Latest reply: Nov 16, 2012 1:28 AM by PeterValencic RSS

    Validating SEPA XML against XSD schema (PL/SQL)

    PeterValencic
      Hello everybody..
      I'm trying to validate an XML file against XSD schema within database package.
      When I validate it in external software xmlspy file passes validation agains it's schema.

      On http://www.xmldb.nl I have found some examples how to register XSD schema and how to validate against XSD but without success..


      1. I have a XSD file (pain.008.001.02.xsd) (http://www.architome.nl/unifi/xml/pain.008.001.02/index.html)
      2. I have large XML (valid in xmlsyp against schema) stored in CLOB on my DB...


      1.) I have registered schema and the schema is available with:
      SELECT * FROM user_xml_schemas;
      output is: SCHEMA_URL = http://localhost/pain.008.001.02.xsd

      2.) In plsql I have this code:
      DECLARE
        l_xml xmltype;
        l_clob clob;
      BEGIN
          select vsebina into l_clob from dok_vsebina_dokumenta_clob where id = 400271904;
          BEGIN
            l_xml := xmltype.createxml(l_clob);
            l_xml := l_xml.createSchemaBasedXML('http://localhost/pain.008.001.02.xsd');
            l_xml.schemaValidate();
          EXCEPTION WHEN OTHERS
             THEN
             dbms_output.put_line ('INVALID => '||sqlerrm);
          END;
        
      END;
      I get the exception:

      INVALID => ORA-31154: invalid XML document
      ORA-19202: Error occurred in XML processing
      LSX-00235: invalid URI "urn:iso:std:iso:20022:tech:xsd:pain.008.001.02"


      Our DB is: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

      Here are first eight lines of my xml
      <Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02">
           <CstmrDrctDbtInitn>
                <GrpHdr>
                     <MsgId>400340878</MsgId>
                     <CreDtTm>2012-11-15T11:06:44</CreDtTm>
                     <NbOfTxs>201</NbOfTxs>
                     <CtrlSum>5790.82</CtrlSum>
                     <InitgPty>
      thank you for any help!
      Peter

      Edited by: peterv6i.blogspot.com on Nov 15, 2012 3:35 PM

      Edited by: peterv6i.blogspot.com on Nov 15, 2012 3:40 PM
        • 1. Re: Validating SEPA XML against XSD schema (PL/SQL)
          odie_63
          Hi Peter,

          You get that error because the <tt>xsi:schemaLocation</tt> attribute is not correct in the instance document :
          xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02"
          It should be in the form "<target namespace> <schema url>" :
          xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02 pain.008.001.02.xsd"
          This works for me :

          Registration
          begin
            dbms_xmlschema.registerSchema(
              schemaURL => 'pain.008.001.02.xsd'
            , schemaDoc => xmltype(bfilename('TEST_DIR','pain.008.001.02.xsd'), nls_charset_id('AL32UTF8'))
            , local => true
            , genTypes => false
            , genTables => false
            , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE  
            );
          end;
          /
          Validation
          declare
            l_xml xmltype;
          begin
            
            l_xml := xmltype(bfilename('TEST_DIR','sepa.xml'), nls_charset_id('AL32UTF8'));
            
            -- This is optional if the correct xsi:schemaLocation is used in the document : 
            -- l_xml := l_xml.createSchemaBasedXML('pain.008.001.02.xsd');
            
            l_xml.schemaValidate();
          
          end;
          /
          tested with sepa.xml :
          <?xml version="1.0" encoding="utf-8"?>
          <Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02 pain.008.001.02.xsd">
          <CstmrDrctDbtInitn>
          <GrpHdr>
          <MsgId>MSGID - 123456</MsgId>
          <CreDtTm>2009-09-04T14:25:00</CreDtTm>
          <NbOfTxs>3</NbOfTxs>
          <CtrlSum>6530</CtrlSum>
          <InitgPty>
          <Nm>Societe XX</Nm>
          </InitgPty>
          </GrpHdr>
          <PmtInf>
          ...
          So you have to make sure that either :

          - the XML instance contains a valid xsi:schemaLocation attribute, where the schema URL matches the one used for registration (in which case you don't need to call the createSchemaBasedXML method)

          - the XML instance does not contain xsi:schemaLocation attribute, and in this case you have to use the createSchemaBasedXML method
          • 2. Re: Validating SEPA XML against XSD schema (PL/SQL)
            PeterValencic
            Thank you for quick response!