translate xml into select statement

we have a system that has data relating to the same business objects in
different formats/databases. So for example, we might have Customers
with their Orders in a SQL Server database and also some information
about Customers and their Orders in a separate Oracle database (legacy
system - two companies merged together).

Anyway, the physical storage of this data may be different in each
database (maybe Order_Date in SQL Server but OrderDt in Oracle - that
kind of thing. Maybe CustomerLocation is in a separate table in SQL
Server whereas in Oracle it is in the same table).

The requirement is to be able to create a generic query that can then
be run into each database as necessary and pull out the required info.
The current proposal is for a standard XML Format that indicates the
Business Entities, the joins of the Business Entities and the filters.
It is proposed that this is sent to a database stored procedure which
will then interpret this XML and return the correct results (back in a
business standard XML).

I think it would be better to:
a) translate the standard XML query to a database specific query using
XSL (because it is easier to maintain AND provides better scaleability
- don't really want the database doing a whole load of
parsing/interpreting to create a dynamic sql statement - especially
since it is an OLTP system and that would impact other users)

b) not sure whether to use the XSL to translate to standard SQL for XML
query or to translate to a mapping-schema which can then be posted to
the virtual directory etc (can it use an in-memory schema rather than
one in the virtual directory, otherwise it means I have to check for a
unique filename first) - any thoughts?

just looking for some suggestions/improvements/confirmation on these
ideas.

thanks
Phil

.

Relevant Pages

  • Re: Another idea from pick goes mainstream...
    ... A relational database has nothing to do ... SQL is one of the ... is now considered THE language for any dbms implementing the relational ... Then, XML came out. ...
    (comp.databases.pick)
  • Re: Poly Couples
    ... business software" example mentioned in this thread. ... But the communication mechanism with the database remains the same. ... That doesn't mean that other languages (such as sql, ... vendor, and the structure is different than the one you use originally. ...
    (comp.object)
  • Re: Deviation from object-relational mapping (pySQLFace)
    ... Its goal to separate relational database stuff from algorythmic ... file (XML). ... It provides callable command objects for each sql query. ...
    (comp.lang.python)
  • Is FOR XML EXPLICIT still an accepted technique?
    ... I have inherited an ASP IIS site attached to a SQL ... Server 2000 database. ... I am new at using XML and have done considerable reading. ...
    (microsoft.public.sqlserver.xml)
  • Re: Parsing large amounts of data (200,000 entries) with XML?
    ... since I know the different uses for a database ... and an XML document. ... I didn't invent that either, ... XML and SQL coexist quite nicely. ...
    (microsoft.public.vb.general.discussion)
Loading