xml-dev - Re: Mechanics of mapping relational data into XML

[Mike Sharp <msharp@lante.com>]

My opinion, looking through 3 tier OO eyes:

Do the domain mapping in the relational engine, or better yet, in your
middleware, if you have it.  You have two, or even three objects represented by
your data:

Product  (table 1 and 2)
    |
    Product_Category  (table 3)
        |
        Product_Component  (also table 3)

The Product Type and Class are simply attributes of the Product Object.  The
Product_Category is a child of the Product object, and it may be argued that the
Product_Component is a child of the Product_Category object.  In that case, I
would denormalize the database to represent that relationship.

Once you have an OO representation of the data, it's pretty easy to convert that
to XML in the same OO fashion.  All you need is a single method to stream the
data in XML.  Write this only once.  There are plenty of tools from the various
database vendors  that produce an XML view on particular database, but I prefer
a 3 tiered approach.  I don't know of any that are commercially available--yet.
The one we're using at the moment is not commercially available.  However it's
possible to build an API of less than a dozen properties, methods and events for
any given data model, if you want to tackle it yourself.  Most of these are used
to build the object with it's children (object.getChildCollect) or it's parent
or set or read certain properties (objectCollect.recordCount).  Only a couple
need to be used for the XML part (I have a setXMLStream, a getXMLStream, and
that's about it).

A separate argument is whether to place object attributes, such as
product_manufacturer or color as a child element in the XML or as an attribute
of an XML tag.  YMMV, but I favor the approach that places object attributes as
child elements, and only use XML attributes for things like security
(ReadOnly="true") or the foreign key of a child object
 (<Product_Category ProductID="123"> ).

I'd represent the data like so:

<products type="Collect">
  <product product_id="123">
    <product_id>123</product_id>
    <product_name>widget1</product_name>
    <product_manufacturer>manufacturerX</product_manufacturer>
    <product_type>type1</product_type>
    <product_class>classX</product_class>
    <product_components type="Collect">
      <product_component CompType="cover">
        <material>plastic</material>
        <color>blue</color>
        <weight_oz>8</weight_oz>
      </product_component>
      <product_component CompType="base">
        <material>metal</material>
        <color>gray</color>
        <weight_oz>6</weight_oz>
      </product_component>
    </product_components>
  </product>
     ...
</products>

Or something like that.  It works for me, anyway.  But there's always a million
different ways to represent the same thing.

Regards,
Mike Sharp

"If you eat only celery and lettuce, you won
't get sick. ... I like celery and
lettuce, but I like it with pickles, relish, corned beef, potatoes, peas. And I
like Eskimo Pies, vanilla ice cream with chocolate covering."  -- Walter Matthau





Jerry Murray <Jmurray@Ironplanet.com> on 07/11/2000 07:47:23 PM

To:   "'xml-dev@lists.xml.org'" <xml-dev@lists.xml.org>
cc:    (bcc: Mike Sharp/Lante)

Subject:  Mechanics of mapping relational data into XML


QUESTION:  When exporting relational data to XML, should:

     a)   the domain model grouping be done by SQL or Java code to creating XML
data, or
Example

Given a relational database with three tables:  x, y ,z

Table x has product information -> one row for each product
     For example:
     product-id  |  product_name  |  product_manufacturer
     123       widget1        manufacturerX
     456       widget2        manufacturerY

Table y has other product information -> one row for each product
     For example:
     product-id  |  product_type  |  product_class
     123       type1          classX
     456       type2          classY

Table z has product category - attribute - value information -> multiple
rows for each product
     For example:
     product-id  |  product_component |  component_attribute |
component_value
     123       cover               material
plastic
     123       cover               color           blue
     123       cover               weight_oz  8
     123       base           material
metal
     123       base           color           gray
     123       base           weight_oz  6
     456       cover               material
plastic
     456       cover               color           blue
     456       cover               weight_oz  4
     456       base           material
metal
     456       base           color           gray
     456       base           weight_oz  7




Jerry Murray
jmurray@ironplanet.com
650/463-4264