XML schema registration and validation in Oracle

Since you don't want generic links, we'll need a lot more information about your specific requirements.   With XML there are many different ways to do any specific task.

For example:
How do you want to validate the XML?  
Do you want to validate it as its inserted into the table or after the fact?
What do you want to do with XML that fails?
Do you have many different XML files in a single table and want to have many different Schemas in another table?

Here's one way as described in ( I basically followed the examples word for word):
http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb05sto.htm#ADXDB0600

The only thing not posted is the XSD file itself.  I copied it from:
http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb03usg.htm#CHDJFCCA
--------------------------------------------------------------------------
drop directory XMLDIR;
create directory XMLDIR as 'C:\';

BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd',
    SCHEMADOC => bfilename('XMLDIR','purchaseOrder.xsd'),
    CSID => nls_charset_id('AL32UTF8'));
END;
/

select local, schema_url from user_xml_schemas;

select table_name from user_xml_tables
  where xmlschema = 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd';


CREATE TABLE purchaseorder_as_column (id NUMBER, xml_document XMLType)
  XMLTYPE COLUMN xml_document
  ELEMENT
    "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd#PurchaseOrder";


--will pass
insert into purchaseorder_as_column values(1,'
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:po="http://xmlns.oracle.com/xdb/documentation/purchaseOrder"
  xsi:schemaLocation=
    "http://xmlns.oracle.com/xdb/documentation/purchaseOrder
     http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd">
  <Reference>SBELL-2002100912333601PDT</Reference>
  <Actions>
    <Action>
      <User>SVOLLMAN</User>
    </Action>
  </Actions>
  <Reject/>
  <Requestor>Sarah J. Bell</Requestor>
  <User>SBELL</User>
  <CostCenter>S30</CostCenter>
  <LineItems>
    <LineItem ItemNumber="1">
      <Description>A Night to Remember</Description>
      <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
    </LineItem>
  </LineItems>
</PurchaseOrder>
');


--will fail (itemNumber isn't a number)
insert into purchaseorder_as_column values(2,'
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:po="http://xmlns.oracle.com/xdb/documentation/purchaseOrder"
  xsi:schemaLocation=
    "http://xmlns.oracle.com/xdb/documentation/purchaseOrder
     http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd">
  <Reference>SBELL-2002100912333601PDT</Reference>
  <Actions>
    <Action>
      <User>SVOLLMAN</User>
    </Action>
  </Actions>
  <Reject/>
  <Requestor>Sarah J. Bell</Requestor>
  <User>SBELL</User>
  <CostCenter>S30</CostCenter>
  <LineItems>
    <LineItem ItemNumber="HellWorld">
      <Description>A Night to Remember</Description>
      <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
    </LineItem>
  </LineItems>
</PurchaseOrder>
');

I have a question on the exmaple provided:
1. command
drop directory XMLDIR;
create directory XMLDIR as 'C:\';
If I don;t have a privelegies to execute this command should I ask DBA or DBA should grant me some privelegies, if DBA needs to grant privelegies which one?

2. I don't have URL now and I want to use file directory on the server or
UTL_FILE directory on Oracle Server. Can I do this
3. I load data into the CLOB data type in Oracle Table.

4. I found this example , but it doesn't work. I'm getting
"error  for parsing -6508 ORA-06508: PL/SQL: could not find program unit being called"

DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'C:\oracle9i\admin\TEST\utl_file\test.xsd',
    SCHEMADOC => bfilename('XMLDIR','test.xsd'),
    CSID => nls_charset_id('AL32UTF8'));


DBA has created xmldir as "create directory xmldir as 'C:\oracle9i\admin\TEST\utl_file';"

I have a question on the example provided:
1. command
drop directory XMLDIR;
create directory XMLDIR as 'C:\';
If I don't have a privileges to execute this command should I ask DBA or DBA should grant me some privileges, if DBA needs to grant privileges which one?

2. I don't have URL now and I want to use file directory on the server or
UTL_FILE directory on Oracle Server. Can I do this
3. I load data into the CLOB data type in Oracle Table.

4. I found this example , but it doesn't work. I'm getting
"error for parsing -6508 ORA-06508: PL/SQL: could not find program unit being called"

DBMS_XMLSCHEMA.registerSchema(
SCHEMAURL => 'C:\oracle9i\admin\TEST\utl_file\test.xsd',
SCHEMADOC => bfilename('XMLDIR','test.xsd'),
CSID => nls_charset_id('AL32UTF8'));


DBA has created xmldir as "create directory xmldir as 'C:\oracle9i\admin\TEST\utl_file';"

1:  As long as the directory has been created for you and you can see it in the ALL_DIRECTORIES view, you're OK.

2:  Are you talking about the SCHEMAURL parameter?  Just make it up.  It's only for internal reference.

3:  Do you mean you want to load the XML data into a CLOB, then at some point in the future, validate it against the schema?

4:  Since we are working on different version, please verify the registerSchema exists in your version:  desc DBMS_XMLSCHEMA.

Silly question:  Does 'C:\oracle9i\admin\TEST\utl_file\test.xsd' exist on the server or your client machine?

Thanks for your feedback.

1. I can see this directory in the ALL_DIRECTORIES.
2.  Does it mean that SCHEMAURL => 'C:\oracle9i\admin\TEST\utl_file\test.xsd' is a valid statement? I will need eventually rename it to the URL
3. This is correct. I'm loading XML data into the CLOB and need to validate against XML schema
4. "desc DBMS_XMLSCHEMA" confirms that registerSchema exists
5.  'C:\oracle9i\admin\TEST\utl_file\test.xsd'  is the Oracle Server Directoryfor UTL_FILE

I'm getting error - "error for parsing -6508 ORA-06508: PL/SQL: could not find program unit being called"

Let's try another canned test case so we're both working from the same code.  I can only test on 10.2 so let's hope 9i will work.

Create a file called purchaseOrder.xsd in C:\oracle9i\admin\TEST\utl_file with the following contents:
-------------------------------------
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" version="1.0">
  <xs:element name="PurchaseOrder" type="PurchaseOrderType"/>
  <xs:complexType name="PurchaseOrderType">
    <xs:sequence>
      <xs:element name="LineItems" type="LineItemsType"/>
    </xs:sequence>
  </xs:complexType>
  <xs:complexType name="LineItemsType">
    <xs:sequence>
      <xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded"/>
    </xs:sequence>
  </xs:complexType>
  <xs:complexType name="LineItemType">
    <xs:attribute name="ItemNumber" type="xs:integer"/>
  </xs:complexType>
</xs:schema>

----------------------------
end copy


Then copy and paste the following into a sql file:
----------------------------------------------------------------

-- just in case it has been run before.
begin
DBMS_XMLSCHEMA.DELETESCHEMA(
    SCHEMAURL => 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd',
      DELETE_OPTION => dbms_xmlschema.DELETE_CASCADE_FORCE
);
end;
/

BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd',
    SCHEMADOC => bfilename('XMLDIR','purchaseOrder.xsd'),
    CSID => nls_charset_id('AL32UTF8'));
END;
/

DROP TABLE purchaseorder_as_column;
CREATE TABLE purchaseorder_as_column (id NUMBER, xml_document CLOB);


--will pass
insert into purchaseorder_as_column values(1,'
<PurchaseOrder>
  <LineItems>
    <LineItem ItemNumber="1"/>
  </LineItems>
</PurchaseOrder>
');


--will fail (itemNumber isn't a number)
insert into purchaseorder_as_column values(2,'
<PurchaseOrder>
  <LineItems>
    <LineItem ItemNumber="HellWorld"/>
  </LineItems>
</PurchaseOrder>
');
commit;

--Check them out
begin
      for i in ( select id, xml_document from purchaseorder_as_column) loop

            dbms_output.put_line('Record ' || i.id || ' is ' ||
                  case xmltype(i.xml_document).isSchemaValid('http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd')
                        when 1 then 'Valid'
                        else 'Not Valid'
                  end
            );

      end loop;
end;
/


----------------------------
end copy



On my 10g test run here was my output:
-------------------------------------
Record 1 is Valid
Record 2 is Not Valid

..on delete schema DBMS_XMLSCHEMA.DELETESCHEMA I'm getting the same error  "error for parsing -6508 ORA-06508: PL/SQL: could not find program unit being called"
Is it possible that my user still reuqired some grants?
I was given "grant  select on DBMS_XMLSCHEMA" Should I have execute as well?

>>Should I have execute as well?

Yes.  You need execute not select on procedures and packages.

Give it a try.  I'm running our of other things to try.  It might also be a bug.  Early 9i was full of them.  Espically in dealing with XML.  What is your full version?

Initially I was mistaken on the privileges provided to my account.
I do have execute privileges on DBMS_XMLSCHEMA package issues by sys.
Still hav "error for parsing -6508 ORA-06508: PL/SQL: could not find program unit being called"
Our server version is Oracle9i Enterprise Edition Release 9.2.0.5.0



I found another example from but having the same issue

http://www.oracle.com/technology/oramag/oracle/03-jul/o43xml.html
SQL> create directory xmldir as '/tmp/xml';
Directory created.


SQL> declare
  2    l_bfile bfile;
  3  begin
  4    l_bfile := bfilename(
                    'XMLDIR', 'po.xsd');
  5    dbms_lob.open(l_bfile);
  6    dbms_xmlschema.registerschema(
  7     'http://asktom.oracle.com/
         ~sdillon/xsd/purchaseOrder.xsd',
  8       l_bfile);

  9    dbms_lob.close(l_bfile);
 10  end;
 11  /

I've done a little more research on that specific error message and everything seems to point to invalid packages.  

Please perform the following:
SELECT * FROM ALL_OBJECTS  WHERE STATUS = 'INVALID';

If you do in fact have invalid packages, run the following as SYS:
@?/rdbms/admin/utlrp

This script will attempt to recompile all packages/procedures in dependency order.

If this doesn't fix it, do you have access to Metalink?  You may have to open an SR with them.

After running this query I found that dbms_xmlsschema has invalid status for owner XDB. I'm not sure if it relates to the same issue.
I'll ask DBA to run @?/rdbms/admin/utlrp

Thanks

After compiling dbms_xmlsschema under the XDB I'm getting a different error.
I'm getting another error:
"non-existing directory or file for FILEOPEN operation"
Here is my two scenarios for schema registration:
1.
-------------------------------------------------------------------------------------------
DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'C:\oracle9i\admin\TEST\utl_file\test.xsd',
    SCHEMADOC => bfilename('XMLDIR','test.xsd'),
    CSID => nls_charset_id('AL32UTF8'));
 
I checked XMLDUR exisats in select* from all_directories
error:  non-existing directory or file for FILEOPEN operation"
---------------------------------------------------------------------------------------------
2. example from http://www.oracle.com/technology/oramag/oracle/03-jul/o43xml.html

CREATE OR REPLACE procedure register_xml_pr is
                                                 
l_bfile bfile;
begin
   l_bfile := bfilename(
                    'XML_DIR', 'test.xsd');
    dbms_lob.open(l_bfile);
   dbms_xmlschema.registerschema('C:\oracle9i\admin\TEST\utl_file\test.xsd',
       l_bfile);
    dbms_lob.close(l_bfile);
exception
when others then
   DBMS_OUTPUT.PUT_LINE(SQLERRM);
end;

error: ORA-01031: insufficient privileges

I resolved the secod issue with error: ORA-01031: insufficient privileges.
It seems like it tracked down to only one problem for both scenarios:
ORA-22285: non-existent directory or file for FILEOPEN operation"

I can see this directory. DBA granted me provelegioes to create directory,
but when I create one and select * from all_directories I still see "SYS" as a owner.

For #1:
------------------------
Please change:
  SCHEMAURL => 'C:\oracle9i\admin\TEST\utl_file\test.xsd',
to
  SCHEMAURL => 'http://xmlns.oracle.com/xdb/documentation/test.xsd',

For #2:
-------------------------
I really don't want to pursue 2 different approaches.

For #1 I'm getting the same error
 "ORA-22285: non-existent directory or file for FILEOPEN operation"
 I think the issue pertains to
SCHEMADOC => bfilename('XMLDIR','test.xsd'),

Please run the following script and see if you get the same error.
--------------------------
DECLARE

      v_bfile                   bfile := BFILENAME( 'XMLDIR' , 'test.xsd');
      v_lob                   clob;


      src_offset  number := 1;
      dst_offset  number := 1;
      cs_id       number := NLS_CHARSET_ID('UTF8'); /* 998 */
      lang_ctx    number := dbms_lob.default_lang_ctx;
      warning     number;

BEGIN

      DBMS_LOB.CREATETEMPORARY(v_lob,TRUE);
      DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
      DBMS_LOB.LOADCLOBFROMFILE(v_lob, v_bfile, DBMS_LOB.GETLENGTH(v_bfile), dst_offset, src_offset, cs_id, lang_ctx,warning);
      DBMS_LOB.CLOSE(v_bfile);

      DBMS_OUTPUT.PUT_LINE('Size: ' || dbms_lob.getlength(v_lob));

      DBMS_LOB.FREETEMPORARY(v_lob);

END;
/

YEs, I'm getting the same error

to make sure that xml_dir works I used the following example from
http://www.oracle-base.com/articles/9i/ParseXMLDocuments9i.php
and it works ...

--my use has privilege to create directory
CREATE DIRECTORY XML_DIR AS ''C:\oracle9i\admin\TEST\utl_file';

CREATE TABLE EMP (
  EMPNO     NUMBER(4),
  ENAME     VARCHAR2(10),
  JOB       VARCHAR2(9),
  MGR       NUMBER(4),
  HIREDATE  DATE,
  SAL       NUMBER(7, 2),
  COMM      NUMBER(7, 2),
  DEPTNO   NUMBER(2));

DECLARE
  l_bfile   BFILE;
  l_clob    CLOB;
  l_parser  dbms_xmlparser.Parser;
  l_doc     dbms_xmldom.DOMDocument;
  l_nl      dbms_xmldom.DOMNodeList;
  l_n       dbms_xmldom.DOMNode;
  l_temp    VARCHAR2(1000);

  TYPE tab_type IS TABLE OF emp%ROWTYPE;
  t_tab  tab_type := tab_type();
BEGIN

  l_bfile := BFileName('XML_DIR', 'emp.xml');
  dbms_lob.createtemporary(l_clob, cache=>FALSE);
  dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
  dbms_lob.loadFromFile(dest_lob => l_clob,
                        src_lob  => l_bfile,
                        amount   => dbms_lob.getLength(l_bfile));
  dbms_lob.close(l_bfile);
 
  -- make sure implicit date conversions are performed correctly
  dbms_session.set_nls('NLS_DATE_FORMAT','''DD-MON-YYYY''');

  -- Create a parser.
  l_parser := dbms_xmlparser.newParser;

  -- Parse the document and create a new DOM document.
  dbms_xmlparser.parseClob(l_parser, l_clob);
  l_doc := dbms_xmlparser.getDocument(l_parser);

  -- Free resources associated with the CLOB and Parser now they are no longer needed.
  dbms_lob.freetemporary(l_clob);
  dbms_xmlparser.freeParser(l_parser);

  -- Get a list of all the EMP nodes in the document using the XPATH syntax.
  l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/EMPLOYEES/EMP');

  -- Loop through the list and create a new record in a tble collection
  -- for each EMP record.
  FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP
    l_n := dbms_xmldom.item(l_nl, cur_emp);

    t_tab.extend;

    -- Use XPATH syntax to assign values to he elements of the collection.
    dbms_xslprocessor.valueOf(l_n,'EMPNO/text()',t_tab(t_tab.last).empno);
    dbms_xslprocessor.valueOf(l_n,'ENAME/text()',t_tab(t_tab.last).ename);
    dbms_xslprocessor.valueOf(l_n,'JOB/text()',t_tab(t_tab.last).job);
    dbms_xslprocessor.valueOf(l_n,'MGR/text()',t_tab(t_tab.last).mgr);
    dbms_xslprocessor.valueOf(l_n,'HIREDATE/text()',t_tab(t_tab.last).hiredate);
    dbms_xslprocessor.valueOf(l_n,'SAL/text()',t_tab(t_tab.last).sal);
    dbms_xslprocessor.valueOf(l_n,'COMM/text()',t_tab(t_tab.last).comm);
    dbms_xslprocessor.valueOf(l_n,'DEPTNO/text()',t_tab(t_tab.last).deptno);
  END LOOP;

  -- Insert data into the real EMP table from the table collection.
  -- Form better performance multiple collections should be used to allow
  -- bulk binding using the FORALL construct but this would make the code
  -- too long-winded for this example.
  FOR cur_emp IN t_tab.first .. t_tab.last LOOP
    INSERT INTO emp
    (empno,
     ename,
     job,
     mgr,
     hiredate,
     sal,
     comm,
     deptno)
    VALUES
    (t_tab(cur_emp).empno,
     t_tab(cur_emp).ename,
     t_tab(cur_emp).job,
     t_tab(cur_emp).mgr,
     t_tab(cur_emp).hiredate,
     t_tab(cur_emp).sal,
     t_tab(cur_emp).comm,
     t_tab(cur_emp).deptno);
  END LOOP;

  COMMIT;

  -- Free any resources associated with the document now it
  -- is no longer needed.
  dbms_xmldom.freeDocument(l_doc);

EXCEPTION
  WHEN OTHERS THEN
    dbms_lob.freetemporary(l_clob);
    dbms_xmlparser.freeParser(l_parser);
    dbms_xmldom.freeDocument(l_doc);
END;
/

I don't see where that sample does anything different than the last sample I posted.

The only thing that I can see is you created a new directory 'XML_DIR' instead of the old one 'XMLDIR'.  This tells me there is a problem with the old 'XMLDIR'.

Try the previous samples of registerschema using XML_DIR instrad of XMLDIR.

I'm not sure exactly what happened, but I'm getting another error
ORA-01031: insufficient privileges
The same procedure was modified by adding registration logic
I create fXML schema emp.xsd

CREATE OR REPLACE procedure test_xml_register as
  l_bfile   BFILE;
  l_clob    CLOB;
  l_parser  dbms_xmlparser.Parser;
  l_doc     dbms_xmldom.DOMDocument;
  l_nl      dbms_xmldom.DOMNodeList;
  l_n       dbms_xmldom.DOMNode;
  l_temp    VARCHAR2(1000);

  TYPE tab_type IS TABLE OF emp%ROWTYPE;
  t_tab  tab_type := tab_type();
BEGIN
 
  DBMS_XMLSCHEMA.registerSchema(
  SCHEMAURL => 'http://xmlns.oracle.com/xdb/documentation/emp.xsd',
  SCHEMADOC => bfilename('XML_DIR','emp.xsd'),
  CSID => nls_charset_id('AL32UTF8'));
 
  DBMS_OUTPUT.PUT_LINE('schema registeration completed');

  l_bfile := BFileName('XML_DIR', 'emp.xml');
  dbms_lob.createtemporary(l_clob, cache=>FALSE);
  dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
  dbms_lob.loadFromFile(dest_lob => l_clob,
                        src_lob  => l_bfile,
                        amount   => dbms_lob.getLength(l_bfile));
  dbms_lob.close(l_bfile);
 
  -- make sure implicit date conversions are performed correctly
  dbms_session.set_nls('NLS_DATE_FORMAT','''DD-MON-YYYY''');

  -- Create a parser.
  l_parser := dbms_xmlparser.newParser;

  -- Parse the document and create a new DOM document.
  dbms_xmlparser.parseClob(l_parser, l_clob);
  l_doc := dbms_xmlparser.getDocument(l_parser);

  -- Free resources associated with the CLOB and Parser now they are no longer needed.
  dbms_lob.freetemporary(l_clob);
  dbms_xmlparser.freeParser(l_parser);

  -- Get a list of all the EMP nodes in the document using the XPATH syntax.
  l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/EMPLOYEES/EMP');
  DBMS_OUTPUT.PUT_LINE('select nodes');
  -- Loop through the list and create a new record in a tble collection
  -- for each EMP record.
  FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP
    l_n := dbms_xmldom.item(l_nl, cur_emp);

    t_tab.extend;

    -- Use XPATH syntax to assign values to he elements of the collection.
    dbms_xslprocessor.valueOf(l_n,'EMPNO/text()',t_tab(t_tab.last).empno);
    dbms_xslprocessor.valueOf(l_n,'ENAME/text()',t_tab(t_tab.last).ename);
    dbms_xslprocessor.valueOf(l_n,'JOB/text()',t_tab(t_tab.last).job);
    dbms_xslprocessor.valueOf(l_n,'MGR/text()',t_tab(t_tab.last).mgr);
    dbms_xslprocessor.valueOf(l_n,'HIREDATE/text()',t_tab(t_tab.last).hiredate);
    dbms_xslprocessor.valueOf(l_n,'SAL/text()',t_tab(t_tab.last).sal);
    dbms_xslprocessor.valueOf(l_n,'COMM/text()',t_tab(t_tab.last).comm);
    dbms_xslprocessor.valueOf(l_n,'DEPTNO/text()',t_tab(t_tab.last).deptno);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('schema parsing');
  -- Insert data into the real EMP table from the table collection.
  -- Form better performance multiple collections should be used to allow
  -- bulk binding using the FORALL construct but this would make the code
  -- too long-winded for this example.
  FOR cur_emp IN t_tab.first .. t_tab.last LOOP
    INSERT INTO emp
    (empno,
     ename,
     job,
     mgr,
     hiredate,
     sal,
     comm,
     deptno)
    VALUES
    (t_tab(cur_emp).empno,
     t_tab(cur_emp).ename,
     t_tab(cur_emp).job,
     t_tab(cur_emp).mgr,
     t_tab(cur_emp).hiredate,
     t_tab(cur_emp).sal,
     t_tab(cur_emp).comm,
     t_tab(cur_emp).deptno);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('data loaded');
  COMMIT;

  -- Free any resources associated with the document now it
  -- is no longer needed.
  dbms_xmldom.freeDocument(l_doc);

EXCEPTION
  WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE(SQLERRM);
    dbms_lob.freetemporary(l_clob);
    dbms_xmlparser.freeParser(l_parser);
    dbms_xmldom.freeDocument(l_doc);

END;
/

Let's break this down into smaller pieces.  You've jumped from the basic registerSchema call to another example with a lot more processing before you even got the registerSchema call to work.

Please go back to my simple loadCLOBfromFile example ( posted: Date:03.02.2007 at 08:50AM EST ) and keep working with it until it works.

Then we'll move on to the simple registerSchema example posted on: Date:02.27.2007 at 04:03PM EST.

Constantly jumping from example to example will only confuse both of us even more.

Thank you.
I think I found one of the issues.

XMLDIR  directory was created by DBA and I was getting error
ORA-22285: non-existent directory or file for FILEOPEN operation

After DBA granted me privelege to create directory I created another object this time called xml_dir
create directory xml_dir as 'C:\oracle9i\admin\TEST\utl_file';

After I used this directory and script you posted on ( posted: Date:03.02.2007 at 08:50AM EST ). It worked fine displying the following ouput:
-------------------------------------------------------------
Size: 1501
ORA-0000: normal, successful completion
PL/SQL procedure successfully completed.
---------------------------------------------------------------

set serveroutput on;
DECLARE
      v_bfile                 bfile := BFILENAME( 'XML_DIR' , 'test.xsd');
      v_lob                   clob;
      src_offset  number := 1;
      dst_offset  number := 1;
      cs_id       number := NLS_CHARSET_ID('UTF8'); /* 998 */
      lang_ctx    number := dbms_lob.default_lang_ctx;
      warning     number;
BEGIN
      DBMS_LOB.CREATETEMPORARY(v_lob,TRUE);
      DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
      DBMS_LOB.LOADCLOBFROMFILE(v_lob, v_bfile, DBMS_LOB.GETLENGTH  (v_bfile), dst_offset, src_offset, cs_id, lang_ctx,warning);
      DBMS_LOB.CLOSE(v_bfile);
      DBMS_OUTPUT.PUT_LINE('Size: ' || dbms_lob.getlength(v_lob));
      DBMS_LOB.FREETEMPORARY(v_lob);
     DBMS_OUTPUT.PUT_LINE(sqlerrm);      
END;

Now what about the registerShcema example?

https://www.experts-exchange.com/questions/22409097/XML-schema-registration-and-validation-in-Oracle.html?cid=238&anchorAnswerId=18620437#a18620437

Thanks for your diligence working on this case. It seems like it was working, but After I run it couple of times different errors appeared.
This is the latest execution, but first time it worked (see messages at the end).

SET SERVEROUTPUT ON;
begin
DBMS_XMLSCHEMA.DELETESCHEMA(
    SCHEMAURL => 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd',
      DELETE_OPTION => dbms_xmlschema.DELETE_CASCADE_FORCE
);
DBMS_OUTPUT.PUT_LINE('DBMS_XMLSCHEMA.DELETESCHEMA COMPLETED');
exception
when others then
DBMS_OUTPUT.PUT_LINE('error for DBMS_XMLSCHEMA.DELETESCHEMA '||SQLERRM);
end;
/

BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd',
    SCHEMADOC => bfilename('XML_DIR','purchaseOrder.xsd'),
    CSID => nls_charset_id('AL32UTF8'));
DBMS_OUTPUT.PUT_LINE('DBMS_XMLSCHEMA.registerSchema COMPLETED');
exception
when others then
  DBMS_OUTPUT.PUT_LINE('error for DBMS_XMLSCHEMA.registerSchema '||SQLERRM);
END;
/

DROP TABLE purchaseorder_as_column;
CREATE TABLE purchaseorder_as_column (id NUMBER, xml_document CLOB);


--will pass
insert into purchaseorder_as_column values(1,'
<PurchaseOrder>
  <LineItems>
    <LineItem ItemNumber="1"/>
  </LineItems>
</PurchaseOrder>
');


--will fail (itemNumber isn't a number)
insert into purchaseorder_as_column values(2,'
<PurchaseOrder>
  <LineItems>
    <LineItem ItemNumber="HellWorld"/>
  </LineItems>
</PurchaseOrder>
');
commit;

--Check them out
begin
      for i in ( select id, xml_document from purchaseorder_as_column) loop

            dbms_output.put_line('Record ' || i.id || ' is ' ||
                  case xmltype(i.xml_document).isSchemaValid('http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd')
                        when 1 then 'Valid'
                        else 'Not Valid'
                  end
            );

      end loop;
end;
/
---------------------------------------------------------------------------------------------------
error for DBMS_XMLSCHEMA.DELETESCHEMA ORA-31000: Resource 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd' is not an XDB schema document
error for DBMS_XMLSCHEMA.DELETESCHEMA ORA-31000: Resource 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd' is not an XDB schema document
PL/SQL procedure successfully completed.
error for DBMS_XMLSCHEMA.registerSchema ORA-22290: operation would exceed the maximum number of opened files or LOBs
PL/SQL procedure successfully completed.
Table dropped.
Table created.
PL/SQL executed.
PL/SQL executed.
Commit complete.
Record 1 is Not Valid
Record 2 is Not Valid
PL/SQL procedure successfully completed.

I also noticed when this script was running as pl/sql blocks it was executed succesfully, but after I put the same scripting into the pl/sql procedure the following error was raised: "ORA-01031: insufficient privileges"
It seems if that some priveledgies reuqired for the procedure that actually executes  package DBMS_XMLSCHEMA.registerSchema.

There appears to be a bug in the registerSchema package that doesn't close the CLOB when it has finished with it.  This shouldn't be a big problem in the production system because you will only register it once.

Eventually, the CLOBs should clean themselves up.  If you are still concerned with this, I'd check out Metalink.  There has to be a fix/patch/work-around for it.

If you can find a solution I would appreciate.
If I register it ones and it works it shouldn't be a problem. Thanks for all your help.