ETL and Data Integration tools - Modern ETL software for everyone

our product is very interesting to me. However I am a little overwhelmed.
I need to be able to:

Read a file name from disk, and the filename will determine the "client
code". So if part of the file name contains "ATICH" then the client code is
"1700.01". Or if part of the name contains MUM then the client code is
"1700.02".
Example File Names:
QA_WESTPOINT_ATICH_NRS_7252011_60005_AM.xml
QA_WESTPOINT_MUM_NRS_7262011_60132_AM.xml

Then I need to read the XML. However the XML contains Groups of records. The basic Structure is like this.

<ClientName>Not used properly by my client, we must get this from the file
name.
<PolicyGroups>
<PolicyGroup>Renewals
<Policies>
<Policy>
Policydetails - the main record
</policy>
</Policies>
<PolicyGroup>
<PolicyGroup>Reinspections
<Policies>
<Policy>
Policydetails - the main record
</policy>
</Policies>
<PolicyGroup>
</PolicyGroups>
<ClientName>

Anyway, I would like the policy details row to contain an identifier for which group the policy is in.
It does not seem possible from my experimentation.

Once I have a table or may two or three that makes sense, I need to load them to a sql server 2008 table(s).

Finally, once the task is completely built and working, I want the process to watch a folder and execute each day to automatically load the records to the tables as defined.

Thank you for any information you can provide that may assist me in this process.

If your product can handle this, I would ever entertain the possibility of paying you to develop this process for me with your tool just because I need it done ASAP.

Thank you,
Tim

Tim

Your example is not well formed XML

Can you email us 3-4 actual XML files so we can do proper testing

Mike

Mike,

Thanks very much.


Check your email I should have more in the morning. They were making a couple of changes to the structure for me. I manually edited this file to match the structure I requested from them in order to do testing on my side.

Also, I was thinking (wondering), if the XML is nested inside other groups, I would have expected a single table when the file was read that contained the additional fields from each group. So in my example, I would have an additional column in the result table that contained the PolicyGroup as a data field. From there I could do anything I want. Just a thought. I don’t want to carry redundant data, but during a transformation this would be helpful. Then I could group on this field etc. Hope that may make some sense.

Thank you again,

Tim

Tim

Good news

Our development team have spent entire day tweaking the software for you and it now works with the file provided.

We can connect remotely now and you will have working solution within next two hours

Mike

Mike,

You guys are great!

Let me know what to do please..

Thanks,
Tim