Re: [xml-dev] XML Schema: Getting lookup tables from DB

["Steve Muench" <Steve.Muench@oracle.com>]

I don't believe this is possible with pure XML Schema to
fetch the valid values out of a database.

Assume we have an "orders.xsd" schema like this:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"; 
      elementFormDefault="unqualified" 
      attributeFormDefault="unqualified">
 <xs:element name="Order">
  <xs:complexType>
   <xs:sequence>
    <xs:element name="Address">
     <xs:complexType>
      <xs:sequence>
       <xs:element name="State">
        <xs:simpleType>
         <xs:restriction base="xs:string">
          <xs:minLength value="1"/>
          <xs:maxLength value="2"/>
         </xs:restriction>
        </xs:simpleType>
       </xs:element>
      </xs:sequence>
     </xs:complexType>
    </xs:element>
   </xs:sequence>
  </xs:complexType>
 </xs:element>
</xs:schema>

With Oracle9i Release 2, you can:

(1) Register your XML Schema document with the database:
 
    exec dbms_xmlschema.registerSchema('orders.xsd', <schemaDoc> );

(2) Create a table with an XMLType column bound to that schema:
 
    create table xmlOrders(
      id         number primary key,
      doc        xmltype,
    )
    xmltype column doc
    xmlschema "orders.xsd" element "Order";

(3) Then write a trigger like this on your table that
    enforces all of your extra database lookups:

    create or replace trigger check_order_document
    before insert on xmlOrders for each row
    declare
      nd xmltype := :new.doc;
      st varchar2(20);
      tmp number;
    begin
      nd.schemaValidate();         /* Full XML Schema validation  */
      select xmlOrders_seq.nextval /* Populate id from a sequence */
        into :new.id 
        from dual;
      -- Enforce a more complicated database lookup on the value of
      -- the XPath expression for state in the doc being inserted
      select 1 into tmp 
        from states 
       where abbrev = extractValue(nd,'/Order/Address/State');
    exception
      when no_data_found then
        raise_application_error(-20001,'Address has invalid state');
    end;


Given this, inserting the following document would fail
XML Schema validation:

<Order xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; 
     xsi:noNamespaceSchemaLocation="orders.xsd">
  <Address>
    <State>CAA</State><!-- value is too long -->
  </Address>
</Order>

This document would fail the database states-table validation

<Order xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; 
     xsi:noNamespaceSchemaLocation="orders.xsd">
  <Address>
    <State>XX</State><!-- value will fail states table validation -->
  </Address>
</Order>

and this one would succeed:

<Order xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; 
     xsi:noNamespaceSchemaLocation="orders.xsd">
  <Address>
    <State>CA</State>
  </Address>
</Order>

assuming our states table had a row for "CA" = "California" in it.

Send me an email if you want a complete demo script to create this
example since I built one to double-check that the syntax above
was solid.

__________________________________________________________________
Steve Muench - Developer, Product Mgr, Java/XML Evangelist, Author
Simplify J2EE and EJB Development with BC4J
http://otn.oracle.com/products/jdev/htdocs/j2ee_bc4j.html
Building Oracle XML Apps, www.oreilly.com/catalog/orxmlapp
----- Original Message ----- 
From: "Long, Craig Z" <craig.long@eds.com>
To: <xml-dev@lists.xml.org>
Sent: Thursday, May 30, 2002 10:50 PM
Subject: [xml-dev] XML Schema: Getting lookup tables from DB


| XML_Dev,
| 
| I have a schema file that will validate large lookup tables.  I'm wanting to
| include another .XSD in my main .XSD that will grab the lookup table
| information.  For example: States are identified by two characters i.e. HI =
| Hawaii,  instead of enumerating these state codes I want to get them from a
| data base.  The state codes are easy, my issue would be codes that identify
| many organizations that may change often -- can this be done using XML
| Schema?
| 
|  
| 
| Regards,
| 
| /**
|  *
|  * @author Craig
|  * @Developer
|  * @Ever Vigilant!!!
|  * 
|  */
|   
| 
| -----------------------------------------------------------------
| The xml-dev list is sponsored by XML.org <http://www.xml.org>, an
| initiative of OASIS <http://www.oasis-open.org>
| 
| The list archives are at http://lists.xml.org/archives/xml-dev/
| 
| To subscribe or unsubscribe from this list use the subscription
| manager: <http://lists.xml.org/ob/adm.pl>
| 
|