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

[Paul Tchistopolskii <paul@qub.com>]

----- Original Message ----- 
From: Ronald Bourret 

> Jerry's database schema
> =======================
> Table x: one row for each product
>   product-id  |  product_name  |  product_manufacturer
> 
> Table y: one row for each product
>   product-id  |  product_type  |  product_class
> 
> Table z: multiple rows for each product
>   product-id  |  product_component |  component_attribute | component_value
> 
> Desired XML
> ===========
>   <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_components>
>   </product>

See  http://www.pault.com/Pxsl/ 'Try advanced'
for online demo of similiar mapping done with 
PXSLServlet ( it composes one XML file out of 
3 'real-life'  tables ).

PXSLServlet  has been designed to support such mappings 
of multiple tables into one 'very hierarhical'  XML file.

<aside>... but don't use attributes ;-). They are evil ;-) What if 
some day you like to turn some field into a pair of 2 fields? 
Attribute is a macroprocessing shortcut for human beings ;-) 
</aside>
 
> >In general, it is still quite fast to let the relational
> >engine do all of your work, then convert to XML after the fact.
> 
> Agreed. It's also a lot easier from the perspective of how much work you 
> have to do.

I don't understand how can we live without SQL->XML mappings 
in the world where most of data reside in SQL databases ( and 
because enterprize XML servers are usualy very sensitive to the 
kind of XPath query being executed ( that means they are not 
yet scalable enough), one should be brave to replace SQL 
server with XML server ... if we are talking about relatively big 
databases, of course ).
 
> >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).
> 
> Note that database-independence is true for virtually all of the middleware 
> listed in the product list -- these are almost all based on JDBC, ODBC, or 
> OLE DB. Only the database-specific products (such as the DB2 XML Extender or 
> SQL Server) don't have this capability.

I have a bit different view. Yes, for example PXSLServlet runs on any 
JDBC database, including even brutal MySQL, but there is no doubt that 
usage of,  for example, Oracle-specific tricks with cursor : 
        allows getting more than 'rowset' - i.e. 'nesting'.
        is faster.
 
There are too many factors here. It is very application-specific, I think.

> [1] 
> http://www.informatik.tu-darmstadt.de/DVS1/staff/bourret/xml/XMLDatabaseProds.htm

Yeah - really *amazing*  list of resources - I have not found anything 
comparable.

*Must*  visit for anyone who is thinking about SQL -> XML mappings...

Rgds.Paul.