[XML-DEV Mailing List Archive Home] [By Thread] [By Date] [Recent Entries] [Reply To This Message]

Re: XML Schema: Getting lookup tables from DB


dbms_xmlschema.registerschema
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@e...>
To: <xml-dev@l...>
Sent: Thursday, May 30, 2002 10:50 PM
Subject:  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>
| 
| 


PURCHASE STYLUS STUDIO ONLINE TODAY!

Purchasing Stylus Studio from our online shop is Easy, Secure and Value Priced!

Buy Stylus Studio Now

Download The World's Best XML IDE!

Accelerate XML development with our award-winning XML IDE - Download a free trial today!

Don't miss another message! Subscribe to this list today.
Email
First Name
Last Name
Company
Subscribe in XML format
RSS 2.0
Atom 0.3
 

Stylus Studio has published XML-DEV in RSS and ATOM formats, enabling users to easily subcribe to the list from their preferred news reader application.


Stylus Studio Sponsored Links are added links designed to provide related and additional information to the visitors of this website. they were not included by the author in the initial post. To view the content without the Sponsor Links please click here.

Site Map | Privacy Policy | Terms of Use | Trademarks
Free Stylus Studio XML Training:
W3C Member
Stylus Studio® and DataDirect XQuery ™are products from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2013 All Rights Reserved.