XQL Challenge (also, Design for Queryability)

Thu Jul 8 15:23:12 BST 1999

Ron,

Here's how it works. You can indeed have lots more
rich structure than what a typicaly flat-relational
table with scalar column types produces.

The tree of tags for a flat relational table looks
like what you mention:

<ROWSET>
  <ROW>
     <FOO>1</FOO>
     <BAR>2</BAR>
  </ROW>
</ROWSET>

when the datatypes are simple scalar types, however there's
much more power when you begin creating your own user-defined
object types and using them directly (or virtually, through
object views) to materialize data that's more structured.

If I create user-defined types NAME and ADDRESS with:

  CREATE TYPE name AS OBJECT(
    first VARCHAR2(40),
    last  VARCHAR2(40)
  );
  CREATE TYPE address AS OBJECT(
    street VARCHAR2(40),
    city   VARCHAR2(40),
    zip    VARCHAR2(10),
  );

and define my table like:

  CREATE TABLE customer (
     custname name,
     home     address,
     work     address
  );

then a SELECT * FROM CUSTOMER will reflect that *extra* structure like:

<ROWSET>
  <ROW>
     <CUSTNAME>
       <FIRST>Steve</FIRST>
       <LAST>Muench</LAST>
     </CUSTNAME>
     <HOME>
       <STREET>101 First St.</STREET>
       <CITY>Redwood Shores</CITY>
       <ZIP>94065</ZIP>
     </HOME>
     <WORK>
       <STREET>22 Northeast South St.</STREET>
       <CITY>Belmont</CITY>
       <ZIP>94066</ZIP>
     </WORK>
  </ROW>
</ROWSET>

If you have a type defined like LINEITEM, then you can
create collection types like:

CREATE TYPE lineitem_collection AS TABLE OF lineitem; (unordered, unbounded)
or
CREATE TYPE lineitem_array AS VARRAY(10) OF lineitem; (ordered, bounded)

Then you can create a "purchase_order" TYPE with an attribute whose
datatype is "lineitem_collection" to get nested collections.

If you're using Object Views, then all of the data can actually
come from flat relational tables if that's where you have it now
or that's where you like to keep it.

So, in one of the demos I show frequently, I have a created a structured
type called CLAIM to model an insurance claim, and use an object view
to materialize data into the rich structure on the fly.

A query over my object view (SELECT VALUE(c) FROM insurance_claim_view c),
a syntax which asks to select the entire top-level CLAIM object in each row,
produces results like:

<RESULTSET>
   <ROW id="1">
      <CLAIM>
         <CLAIMID>77804</CLAIMID>
         <FILED>1999-01-01 00:00:00.0</FILED>
         <CLAIMPOLICY>
            <POLICYID>8895</POLICYID>
            <PRIMARYINSURED>
               <CUSTOMERID>1044</CUSTOMERID>
               <FIRSTNAME>Paul</FIRSTNAME>
               <LASTNAME>Astoria</LASTNAME>
               <HOMEADDRESS>
                  <STREET>123 Cherry Lane</STREET>
                  <CITY>SF</CITY>
                  <STATE>CA</STATE>
                  <ZIP>94132</ZIP>
               </HOMEADDRESS>
            </PRIMARYINSURED>
         </CLAIMPOLICY>
         <SETTLEMENTS>
            <SETTLEMENTS_ITEM itemNo="1">
               <PAYDATE>1999-01-05 00:00:00.0</PAYDATE>
               <AMOUNT>7600</AMOUNT>
               <APPROVER>JCOX</APPROVER>
            </SETTLEMENTS_ITEM>
         </SETTLEMENTS>
         <DAMAGEREPORT>
           Car ran into a ditch. On-site inspector
           determined that <CAUSE>Faulty Brakes</CAUSE>
           were the cause.
         </DAMAGEREPORT>
      </CLAIM>
   </ROW>
   <ROW id="2">
      <CLAIM>
         <CLAIMID>12345</CLAIMID>
         <FILED>1998-03-11 00:00:00.0</FILED>
         <CLAIMPOLICY>
            <POLICYID>8895</POLICYID>
            <PRIMARYINSURED>
               <CUSTOMERID>1044</CUSTOMERID>
               <FIRSTNAME>Paul</FIRSTNAME>
               <LASTNAME>Astoria</LASTNAME>
               <HOMEADDRESS>
                  <STREET>123 Cherry Lane</STREET>
                  <CITY>SF</CITY>
                  <STATE>CA</STATE>
                  <ZIP>94132</ZIP>
               </HOMEADDRESS>
            </PRIMARYINSURED>
         </CLAIMPOLICY>
         <SETTLEMENTS>
            <SETTLEMENTS_ITEM itemNo="1">
               <PAYDATE>1998-03-15 00:00:00.0</PAYDATE>
               <AMOUNT>1800</AMOUNT>
               <APPROVER>MFOX</APPROVER>
            </SETTLEMENTS_ITEM>
            <SETTLEMENTS_ITEM itemNo="2">
               <PAYDATE>1998-03-23 00:00:00.0</PAYDATE>
               <AMOUNT>7800</AMOUNT>
               <APPROVER>ULOWE</APPROVER>
            </SETTLEMENTS_ITEM>
         </SETTLEMENTS>
         <DAMAGEREPORT>
            It appears the driver rammed his car into
            the embankment due to <CAUSE>Excess of Speed</CAUSE>.
         </DAMAGEREPORT>
      </CLAIM>
   </ROW>
</RESULTSET>

Lastly, you have full queryability over the virtual object
structure in SQL using SQL3 extended path notation, so you
can select just the CUSTOMER substructure from the insurance
claim view like:

select c.claimpolicy.primaryinsured as Customer
  from insurance_claim_view c
 where c.claimpolicy.primaryinsured.homeaddress.state = 'CA'

The query performance is the same as if you did it against
the flat underlying tables with the yuckier syntax and
did the joins yourself.

Have fun...

----- Original Message -----
From: Ronald Bourret <rbourret at ito.tu-darmstadt.de>
To: <xml-dev at ic.ac.uk>
Sent: Thursday, July 08, 1999 4:41 AM
Subject: RE: XQL Challenge (also, Design for Queryability)

xml-dev: A list for W3C XML Developers. To post, mailto:xml-dev at ic.ac.uk
Archived as: http://www.lists.ic.ac.uk/hypermail/xml-dev/ and on CD-ROM/ISBN 981-02-3594-1
To (un)subscribe, mailto:majordomo at ic.ac.uk the following message;
(un)subscribe xml-dev
To subscribe to the digests, mailto:majordomo at ic.ac.uk the following message;
subscribe xml-dev-digest
List coordinator, Henry Rzepa (mailto:rzepa at ic.ac.uk)