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

[Joshua Allen <joshuaa@microsoft.com>]

Title: RE: Mechanics of mapping relational data into XML

IBM have an XML Extender for DB2 (free download on alphaworks or developerworks),

and MS SQL Server allows selecting to XML using a specified schema or automatic

(e.g. SELECT * FROM authors FOR XML AUTO).  I am sure other vendors can do

this now, too.  Besides persisting the recordset object to XML (as in ADO mentioned

below) and querying to get XML back, there are plenty of other ways to get XML

from a relational database.  I can think of 5 or 10 at least..

 

In a more generic sense, I read the first part of the question to be something

like:  "Should I join data first, then produce XML, or should I produce XML, then

join?"  (oversimplified a bit..)

 

I think this is similar to the situation when doing a distributed join, and you want

to know what is "best"?  Should I:

 

1) Ship results from A to B, then join

2) Ship results from B to A, then join

3) Ship results from both A and B to client and then join

 

In fact, it depends on the query.  That is why distributed

cost-based query optimizers are still worth paying for.

IMHO, the same is true with XML -- sometimes one technique

makes sense, and sometimes another.  Even things like your

caching strategy can change which technique is "best" for

a given query.  In general, it is still quite fast to let the relational

engine do all of your work, then convert to XML after the fact.

However there are certainly cases where this is not true.

 

Another thing to note about the technique Kar Yan mentioned

below is that you can be fairly database-agnostic.  All of the

major databases out there seem to have different syntax for

returning XML directly, but ADO always returns a recordset, no

matter what database you use.  If you convert from ADO to

recordset, you can be programming-language agnostic and

database agnostic.  (If you need your code to run on platforms

other than Windows and can use pure Java, I am sure that

Java recordsets now have [or coming soon] ability to persist

to XML generically).

-----Original Message-----
From: Kar Yan Ng [mailto:kyng@nQuire.com]
Sent: Tuesday, July 11, 2000 8:35 PM
To: 'Jerry Murray'; 'xml-dev@lists.xml.org'
Subject: RE: Mechanics of mapping relational data into XML

Does this sound like the functionality provided by the MSPersist and MSDataShape data provider in Microsoft ADO 2.5?

http://msdn.microsoft.com/xml/articles/xmlintegrationinado.asp

Kar Yan

-----Original Message-----
From:   Jerry Murray [mailto:Jmurray@Ironplanet.com]
Sent:   Tuesday, July 11, 2000 7:47 PM
To:     'xml-dev@lists.xml.org'
Subject:        Mechanics of mapping relational data into XML




I would like to get some suggestions for the mechanics of mapping relational
data into XML for situations where the mapping requires grouping of data.

QUESTION:  When exporting relational data to XML, should:

        a)      the domain model grouping be done by SQL or Java code prior
to creating XML data, or
        b)      should XML data be created for each table, then have an
application parse  the "XML" table documents and write the new desired XML
document.
        c)      or ... can transformations be performed (e.g. using
something like RELAX) to go from the "XML" table documents to the final
single XML document
        d)      or... are any tools available that already handle this
situation.




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




WANTED:

        <product>
                product_id="123"</product_id>
                product_name="widget1"
                product_manufacturer="manufacturerX"
                product_type="type1"
                product_class="classX"
                <product_components>
                        <cover material=plastic color="blue" weight_oz="8"
/>
                        <base material=metal color="gray" weight_oz="6" />
        </product>
        <product>
                product_id>456"
                product_name="widget2"
                product_manufacturer="manufacturerY"
                product_type="type2"
                product_class="classY"
                product_components>
                        <cover material=plastic color="blue" weight_oz="4"
/>
                        <base material=metal color="gray" weight_oz="7" />
        </product>





Thanks,

Jerry




===================




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