[XML-DEV Mailing List Archive Home] [By Thread] [By Date] [Recent Entries] [Reply To This Message] Re: 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" 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! Download The World's Best XML IDE!Accelerate XML development with our award-winning XML IDE - Download a free trial today! Subscribe in XML format
|