OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.


Help: OASIS Mailing Lists Help | MarkMail Help



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

[ Lists Home | Date Index | Thread Index ]

Thanks Steve,

Can I query the tables to create a xml file and store it as a datagram to be
used by my schema?  In other words, can my schema reference a xml file (that
will contain lookup table information)? That way if/when the table changes
all one would have to do is create an updated xml file.


 * @author Craig
 * @Developer
 * @Ever Vigilant!!!

-----Original Message-----
From: Steve Muench [mailto:Steve.Muench@oracle.com]
Sent: Thursday, May 30, 2002 5:55 PM
To: Long, Craig Z; xml-dev@lists.xml.org
Subject: Re: [xml-dev] XML Schema: Getting lookup tables from DB

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"; 
 <xs:element name="Order">
    <xs:element name="Address">
       <xs:element name="State">
         <xs:restriction base="xs:string">
          <xs:minLength value="1"/>
          <xs:maxLength value="2"/>

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
      nd xmltype := :new.doc;
      st varchar2(20);
      tmp number;
      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');
      when no_data_found then
        raise_application_error(-20001,'Address has invalid state');

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

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

This document would fail the database states-table validation

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

and this one would succeed:

<Order xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; 

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
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
| 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
| data base.  The state codes are easy, my issue would be codes that
| 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>


News | XML in Industry | Calendar | XML Registry
Marketplace | Resources | MyXML.org | Sponsors | Privacy Statement

Copyright 2001 XML.org. This site is hosted by OASIS