|
|
 next
|
  Subject: Optimal for DB to XML?
Author: Rob Oaks
Date: 08 Aug 2014 03:04 PM
|
I am trying to determine whether Stylus Studio is an optimal solution for the following scenario:
1. Source is a SQL Server database containing insurance policy information. Database schema is predetermined and includes perhaps a dozen tables with various one-to-many, and many-to-many relationships.
2. The top of the database hierarchy is the policy number. Given a policy number, it is possible to write a set of queries that extracts all the data that pertains to that policy. This includes a record in the Policy table (contains basic insured information), all of the records in the Risk table (represents autos, houses, etc. covered by the policy) that are related to the Policy record, and all of the records in the Coverage table (represents coverages for each Risk record) that are related to each Risk record.
3. The bottom line goal is to create a black box that, given a policy number, extracts the relevant data from the database and create a hierarchical XML representation of that policy.
IMPORTANT:
1. Preferably I would like to auto-generate an XML schema (XSD) from the database and use this XSD as the target of the extraction.
2. No transformations will be performed on the data and I am not too concerned about the quality of the XSD that is generated because I’m only doing this as a way to “re-source” the data for transmission (see the next point).
3. All I care about is transforming the data for the specified policy to an XML string with no loss of information. I will then be transmitting that string via Web Service to a server that will place it in a message queue, where the transformations and detailed processing (I will use XQuery to extract and transform the data) will occur. The resulting XML will typically be less than 200K, though the source database could be many Gb.
4. I need to be able to turn the MapForce code into Java source, so that I can ultimately create a JAR that I can deploy on a server somewhere.
Remember: I already have an ETL platform/process; I just need a way to transform a set of hierarchical database records to XML for wire transfer.
Is Stylus Studio a good solution for this scenario? Can you outline the steps that would be required in Stylus Studio?
Thanks so much!
|
|
|
|
top