xml-dev - Re: XQL Challenge (also, Design for Queryability)

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

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@ito.tu-darmstadt.de>
To: <xml-dev@ic.ac.uk>
Sent: Thursday, July 08, 1999 4:41 AM
Subject: RE: XQL Challenge (also, Design for Queryability)


| Steve Muench wrote:
|
| > If your data is already in a database, and you already
| > know the SQL that gets you the info you need, why not
| > combine these two pieces of info with a utility like
| > the Oracle XML SQL Utility for Java and...
| >
| >  -> Query the data with full speed and power of your database
| >  -> Query it from any point of view required for the app at hand
| >  -> Return on-the-fly XML results from what you find
| >
| > Using something like an Object View you can predefine
| > a rich structure to your data, query it using SQL3 object
| > query syntax, and get the rich structure back in the XML
| > for the results. You can see the "Insurance Claim" demo
| > that comes with the Oracle XSQL Servlet for an example.
|
| I've looked at the examples, but unfortunately don't have an Oracle
| database available at the moment to execute them. The documentation states
| that the output of a SELECT statement is returned as XML nested three
| levels deep: rowset, row, and column. Do object views allow you to get
| deeper nesting?
|
| For example, suppose I have the following tables:
|
|    SalesOrders
|       SONumber
|       CustNumber
|
|    Lines
|       SONumber
|       LineNumber
|       PartNumber
|       Quantity
|
| Can I then define an object view over these tables such that I have
| SalesOrder objects, each of which has an array of Lines objects?  And
| supposing I can do this, does executing a SELECT statement in a document
| processed by the XSQL Servlet producing a flat or nested XML document?
That
| is, do I get the following document:
|
|    <rowset>
|       <SalesOrder>
|          <SONumber>...</SONumber>
|          <CustNumber>...</CustNumber>
|          <Line>
|             <LineNumber>...</LineNumber>
|             <PartNumber>...</PartNumber>
|             <Quantity>...</Quantity>
|          </Line>
|          ...
|          <Line>
|             <LineNumber>...</LineNumber>
|             <PartNumber>...</PartNumber>
|             <Quantity>...</Quantity>
|          </Line>
|       </SalesOrder>
|    </rowset>
|
| or are there still only three levels of nesting, with SONumber and
| CustNumber repeated for each Line:
|
|    <rowset>
|       <SalesOrder>
|          <SONumber>...</SONumber>
|          <CustNumber>...</CustNumber>
|          <LineNumber>...</LineNumber>
|          <PartNumber>...</PartNumber>
|          <Quantity>...</Quantity>
|       </SalesOrder>
|       ...
|       <SalesOrder>
|          <SONumber>...</SONumber>
|          <CustNumber>...</CustNumber>
|          <LineNumber>...</LineNumber>
|          <PartNumber>...</PartNumber>
|          <Quantity>...</Quantity>
|       </SalesOrder>
|    <rowset>
|
| Thanks.
|
| -- Ron Bourret
|
|
| xml-dev: A list for W3C XML Developers. To post, mailto:xml-dev@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@ic.ac.uk the following message;
| (un)subscribe xml-dev
| To subscribe to the digests, mailto:majordomo@ic.ac.uk the following
message;
| subscribe xml-dev-digest
| List coordinator, Henry Rzepa (mailto:rzepa@ic.ac.uk)
|
|


xml-dev: A list for W3C XML Developers. To post, mailto:xml-dev@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@ic.ac.uk the following message;
(un)subscribe xml-dev
To subscribe to the digests, mailto:majordomo@ic.ac.uk the following message;
subscribe xml-dev-digest
List coordinator, Henry Rzepa (mailto:rzepa@ic.ac.uk)