5 Replies Latest reply: Mar 9, 2010 4:32 AM by 690304 RSS

    10g xmldb ORA-31000: is not an XDB schema document

    478557
      Hi All,
      I'm working on oracle 10g. I have followed the steps defined here http://www.utexas.edu/its/unix/reference/oracledocs/v92/B10501_01/appdev.920/a96620/xdbxcases2.htm.

      I have added my xml schema under oracle server, and registered it with
      begin
      dbms_xmlschema.registerSchema
      (
      'server url:8080/public/myxml/xsd/MyMain.xsd',
      xdbURIType('/public/myxml/xsd/MyMain.xsd').getClob(),
      True,True,False,True
      );
      end;
      /

      than i try to create a table dependent on this schema

      CREATE TABLE SHOPLIST
      (
      ID NUMBER(15) NOT NULL,
      NAME VARCHAR2(255 BYTE),
      XML_DATA SYS.XMLTYPE
      )
      xmltype column XML_DATA
      xmlschema "http://server url::8080/public/myxml/xsd/MyMain.xsd" element "root";

      but i get this error:
      ERROR at line 1:
      ORA-31000: Resource 'http://serverurl:8080/public/myxml/xsd/MyMain.xsd' is not
      an XDB schema document
        • 1. Re: 10g xmldb ORA-31000: is not an XDB schema document
          396018
          issue this command.
          select schema_url from user_xml_schemas
          /

          most likely you should get the below url as the result.

          server url:8080/public/myxml/xsd/MyMain.xsd

          if yes then try the below script.

          CREATE TABLE SHOPLIST
          (
          ID NUMBER(15) NOT NULL,
          NAME VARCHAR2(255 BYTE),
          XML_DATA SYS.XMLTYPE
          )
          xmltype column XML_DATA
          xmlschema "server url:8080/public/myxml/xsd/MyMain.xsd" element "root"
          /

          Once the schema is registered in the databas,e it is always refered with the url you gave during the registration process (i.e the first parameter to the registerschema).
          • 2. Re: 10g xmldb ORA-31000: is not an XDB schema document
            478557
            it returns like this,
            SQL> select schema_url from user_xml_schemas
            2 /

            SCHEMA_URL
            --------------------------------------------------------------------------------
            http://0.0.0.0:8080/public/myxml/xsd/MyMain.xsd

            but it doesn't work.
            I have registered the schema as system user,but now i'm trying to create this table under
            ordinary user. do i have gain any priviliges to see or use the registered schemas?
            can it be because of something like that?
            • 3. Re: 10g xmldb ORA-31000: is not an XDB schema document
              396018
              Viki, then you should register it as global schema and not local.
              The third arguement in the registerschema should be FALSE.
              • 4. Re: 10g xmldb ORA-31000: is not an XDB schema document
                478557
                Thanks a lot Raghu,
                that worked fine. Now i can check schema validity when inserting xml documents.

                But i have another problem now:( Before relating my xml to the schema i could select it from java. But now i get the error:
                java.sql.SQLException: Only LOB or thin Storage is supported in Thin XMLType,
                with the same code.

                can it have something again related to my schema?
                can you please help i think i loose the big picture here.

                Thanks
                viki
                • 5. Re: 10g xmldb ORA-31000: is not an XDB schema document
                  690304
                  viki wrote:
                  Thanks a lot Raghu,
                  that worked fine. Now i can check schema validity when inserting xml documents.

                  But i have another problem now:( Before relating my xml to the schema i could select it from java. But now i get the error:
                  java.sql.SQLException: Only LOB or thin Storage is supported in Thin XMLType,
                  with the same code.

                  can it have something again related to my schema?
                  can you please help i think i loose the big picture here.

                  Thanks
                  viki
                  When I got the same weird error, my guess was:
                  It seems that the Oracle thin driver does not support instantiation of xmltype with object relational storage.
                  I got it to work with the following workaround:

                  sql = "INSERT INTO mytable (myid, myOtherAttribute, xml) VALUES (?, ?, XMLTYPE(?))";

                  CLOB clob = DatabaseUtils.getCLOB(con, xmlCodeAsString);

                  stmt = con.prepareStatement(sql);

                  stmt.setLong(1, myid);
                  stmt.setInt(2, myOtherAttribute);
                  stmt.setObject(3, clob);

                  stmt.executeUpdate();

                  clob.freeTemporary();


                  with DatabaseUtils.getCLOB():

                  public static CLOB getCLOB(Connection con, String clobData) throws Exception {

                  if (StringUtils.isEmpty(clobData)) {
                  return null;
                  }

                  CLOB tempClob = null;
                  try {

                  OracleConnection ocon = (OracleConnection) con;

                  // create a new temporary CLOB
                  tempClob = CLOB.createTemporary(ocon, true, CLOB.DURATION_SESSION);

                  // Open the temporary CLOB in readwrite mode to enable writing
                  tempClob.open(CLOB.MODE_READWRITE);

                  tempClob.setString(1, clobData);

                  // Close the temporary CLOB
                  tempClob.close();

                  } catch (Exception exp) {
                  // Free CLOB object
                  tempClob.freeTemporary();

                  throw exp;
                  }
                  return tempClob;

                  }