Re: Access to heterogeneous data sources - XML

[Quoted] [Quoted] "Andrea M. Segovia" <andrea_at_mun.ca> wrote in message news:7589088f.0403080637.63c9f418_at_posting.google.com...
> Hello, everyone:
>
> We have a Oracle 3 tier application (using Oracle iAs to a back-end
> Oracle database on an Tru64 UNIX server) which also incorporates
> custom written analysis and mapping tools for fisheries-related data.
>
> There is interest in broadening the scope of this application to also
> gather data from other data sources. The problem is that the other
> data sources are not Oracle databases (some are MS-SQL Server, others
> are MS-Access databases). Moving data to an Oracle format is not a
> possibility, as some of these data sources are out of our
> organization's control. To complicate matters even further, some of
> the data sources are not local (ie. in remote locations in either
> internal or external networks from one end of the country to another).
>
> Is XML a possibility for this? What XML functionality is available
> through Oracle, MS-SQL server? How would I do this using XML?
>
> Regards,
> Andrea

XML is basically a file format and that in and by itself isn't going to help [Quoted] [Quoted] you. Currently, it is a big buzz word. Concentrate on the business problem [Quoted] and what a solution to your problem would look like. This will help you to [Quoted] come up with solutions that would fit the problem. (instead of a hammer and [Quoted] everything is a nail.)

[Quoted] You are basically talking about using an ETL tool. It is unlikely that the [Quoted] fishery data is stored in the same structures in all those different [Quoted] databases. That is not the same table and table name with columns that are [Quoted] matching in data type and name. (and meanings) So for each data source you [Quoted] [Quoted] will need to know what the "transformation" is so you can read the data. Major job. If the data sources are out of your control then it is unlikely [Quoted] you will be able to see them or connect to them.

[Quoted] Your best bet is probably to treat your central system as a data warehouse [Quoted] and copy the data to your system periodically. How to do that? You could [Quoted] load a java SQLServer driver into Oracle and connect to all those SQLServer [Quoted] databases and grab the data.(the other problem is that things won't always [Quoted] be up or available to connect to.) You would still have to transform the [Quoted] data into some common schema etc. Again , a highly detailed task. [Quoted] As for the MS Access databases, you could probably find some driver that can [Quoted] read those and use SQL to get at the data. Again you are going to have a lot of mapping to do.

Jim Received on Wed Mar 10 2004 - 03:35:41 CET