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

[Ronald Bourret <rpbourret@hotmail.com>]

Michael Rys wrote:

>Please note that SQLServer 2000 (and the SQLXML tech preview) infer nesting
>information from the SQL query based on the join/Cartesian product in the
>FOR XML auto mode mentioned below). One limitation of this (and the 
>approach
>mentioned in the WebTechnique article) is that you only get single type
>hierarchies (i.e. all children are of the same type, e.g. Order contains
>Customer) and there is no way to identify sibling instance data of a
>different type solely based on the resulting rowset of a simple join query.

Could you explain this further? I'm not sure I understand what you mean by 
"single type hierarchy". Do you mean that you couldn't infer that B and C 
are both children of A in the following statement?

   SELECT A.a, B.b, C.c FROM A, B, C
   WHERE A.a = B.a AND A.a = C.a

If not, why not?

>Also the mapping schema approach of SQLServer provides the "model-driven"
>approach by annotating an XDR schema and translating an XPath expression
>against that schema into the corresponding SQL expression.

One point here that I think is relevant -- "model-driven" does not imply 
that the user can explicitly map the data. Both SQL Server's FOR XML AUTO 
and FOR XML EXPLICIT are model-driven. This is because both use the same 
underlying model: they view the data as a tree of data-specific objects and 
use an object-relational model to map this to/from the database. (In a 
template-driven approach, there is no underlying model -- the user simply 
says, "Get the data according to the following query and put it here, and 
here, and here.")

>Ronald hints also at another categorization of mappings of relational data
>to XML:
>
>- Canonical mappings such as
>
><tables>
>  <table>
>   <row>
>    <column>...</column>
>    ...
>   </row>
>   ...
>  </table>
>  ...
></tables>
>
>which are generated by tools such as Oracle's XML query templates, ADO's 
>XML
>persist format.

Actually, if I've read the Web pages correctly, both Oracle's XML SQL 
Utility for Java and ADO are not limited to a table model and support a 
tree-of-objects model, which means arbitrarily deep nesting in XML. However, 
Oracle requires that the database support SQL 3 object views and ADO 
supports this only in version 2.5 (it didn't support this in version 2.1).

>This format is efficiently generated but needs a lot of (potentially)
>inefficient postprocessing to generate the application's XML structure.

Yes.

>- Heuristic mappings such as the FOR XML auto mode, or the WebTechnique
>article. A semantic XML structure is inferred from the underlying 
>datamodel.
>Closer to the goal if the database model corresponds to the application's
>model.
>
>- Mapping descriptions such as FOR XML explicit, mapping schemata, or IBM's
>DADs. They generate the desired XML structure with some mainly relational
>limitations w.r.t. to pivoting relational data into meta-information such 
>as
>the issue mentioned below w.r.t. transforming values into XML element 
>names.
>
>The over-/non-normalized issue identified by Ronald is an issue in the 
>first
>two approaches and would probably be best solved via an intermediate,
>normalized view. The mapping approaches (such as SQLServer's mapping 
>schema)
>should not have a problem with data being combined from 
>over-/non-normalized
>tables, assuming that some identification for the relationships is
>available.

Actually, whether products that provide mapping tools can support such 
non-normal data depends on the mapping tools and the data transfer software. 
For example, although my product (XML-DBMS) can handle the proposed case, 
this is a bit of a fluke and, in general, it can't normalize/de-normalize 
non-normal/over-normal data. This is because the mapping language does not 
require users to write SQL statements (the software generates them instead). 
Ah, those generality/ease-of-use tradeoffs...

>As to platform independence ... [snip] the issue is that there is not (yet) 
>a
>standardized way to tell a relational database on how to generate XML.

Has anybody started thinking about this yet? I think there are really three 
issues here:

1) The underlying model to be used,
2) How this is mapped to the database, and
3) The actual mapping language (or whatever it turns out to be).

I think the first part won't be too difficult -- it's really just a matter 
of deciding what parts of an XML document are worth saving in a relational 
database and how to model them. Given that all the large database companies 
and a number of the middleware providers have already implemented a 
tree-of-objects model, the only real questions here will be some niggly bits 
-- is mixed content supported? How are ID/IDREF attributes modeled? Is order 
in sibling elements and multi-valued attributes significant? etc.

The second and third parts will be rather more difficult. Not only are there 
the usual hassles about how to name the mapping language elements, it's not 
even clear what the form of the language is or what degree of features it 
supports. For example, DAD and XML-DBMS use proprietary, XML-based mapping 
languages that don't require the user to write SQL statements. SQL Server 
appears to require SQL statements, and then marks up XML Schema. Beanstalk 
(another middleware tool) simply adds a bit of extra syntax to SQL. All of 
these have different features and different capabilities, all with different 
performance.

In spite of these problems, I do think that an XML <=> RDBMS will be 
standardized at some point in the future. It's just a question of when...

-- Ron
________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com