I'm no DB by any means and I have a question I need answered. Well more like a 5 part question...
Can OVSD connect to and use XML as a datasource for Dataexchange?
What XML formats & DTD's does OVSD like? Does it have to be a certain version/format?
Is DataExchange in OVSD compatible w/ Microsoft XML (same XML that would come from a SQL 2005 dump)
Basically what we want to happen is this, connect to various data sources using a Pearl Script via WIN32 ODBC connections that pulls a dump from the sources and creates an XML file for each seperate table w/ its own schema. Then configure Dataexchange tasks to pull info directly from the XML files and import into OVSD.
I really need some answers to this soon. I also appreciate your time and input.
I have not done this with xml, but here is my best guess (and I do a lot of data exchange in 5.1). OVSD data exchange will pull from any odbc source. I have set up a few to pull in data from excel. I set up the odbc datasource on the OVSD server and then configure the data exchange ini to connect to the dsn. The rest is standard data exchange stuff. So, the two things here â first, find an xml odbc driver (it is not standard on my installation) and second, imports from files are fussy and problematic. An easier way might be to have the script dump the data into a temp SQL table and then bring it in.
Then again (thinking out loud here)since the exchanger first converts it to xml before mapping it in, why not get you script to create OVSD friendly xml directly (since you are doing xml anyway)? Just a thought.
We run data exchanges from 3 separate sources twice a day. I create a separate db on the sql server and then created views that model the data the way I want it. I then connect to those views and import.
Thanks that clears things up a bit. I imagine creating an import mapping for a XML datasource would be possible? Thoughts...
Creating SQL tables is not an option at this point, we want to pull from many sources into XML then create the mapping (ini file) based on that XML dump. This would not be OVSD connecting and creating the XML file rather using (like u said) the xml odbc driver and connecting directly to those files via OVSD DE.
So OVSD can connect directly to the XML files created from a SQL query?
I'm understanding what you're saying but one thing has me confused, The XML file has been created, but no .ini file for mapping is created, do you NEED to connect to the XML file via an ODBC driver in order for the extractor provided w/ OVSD to "correctly format" that XML file into another XML for import??
Marc, you do not need the ini file if you use this method. The ini file is used to define what fields and data are put into the xml file. You are creating the xml through a seperate process and so no ini required. What this means then is that when you run the data exchange you only run the "Import" portion and not the "Export". The part you may be missing is to point to the xml file in the "Exchange File" section of the DA dialog box.
Do you, or do you NOT need to use the extractor supplied with OVSD if you have an xml file already?
Reason I ask is, how would you go about defining CLASS + ATTRIBUTES etc.. for import, without first having performed the export from a datasource using the extraction tool in OVSD.
How would you go about using an XML file that was created via perl scripting with it's own XML Schema (no DTD file) that pulled all info from say a SQL Db?
I'm having a very hard time understanding what is REQUIRED in order to gather info from various sources without having used and ODBC/JDBC driver to connect to and pull the information into an XML file OVSD will be able to work with.
Sorry guys, I'm an idiot, but what I see written above contradicts what is in the help files for OVSD itself.