Rank: Newbie
Joined: 7/20/2020
Posts: 1
Location: Hilton Head, SC
|
I have 3900 xml files sitting in a directory.
On a weekly basis I manually download, from our SAS provider, approximately 140 or so to add to the count.
They are needing to be imported / exported into a sql server 2014r2 database, which I need to create, along with any relational tables. Not sure how one flat file table would handle 2800 fields.
I have no programing experience in xml, do know they are a bunch of tags...LOL. Just started this past week on Altov XMLSpy 2020.
Before my 30 day license expires, I'm trying to find out if someone with no experience in either can easily:
1. Create a database / and relational tables with the xml schema internally. The xmls have about 2800 tags in total, but not all are used at the same time. I'm familiar enought with sql to get this done, manually, which would take me weeks.
2. Insert or poll the directory where the xml files are in / downloaded and automatically get them imported into the sql server. Once I manually get them using FTP.
3. Would really like to have all this automated if possible.
4. I'm trying to get this imported by field / tag. This would help me use SSRS to create custom reports for my organization.
Now my questions
Since I don't know what I don't know, I'm wondering if Altova can do any / all of the above?
How hard or easy would it be?
Am I way over my head on this?
I've been able to obtain a testxml and related xsd file.
|
Rank: Advanced Member
Joined: 2/27/2009
Posts: 564
|
Well, that's a lot of ground to cover.
2800 "fields": if your XML structure is simply a repeating element with 2800 fields (attribute or element), then it's fairly straightforward to convert it to a database table. You will likely run into limitations of the number of columns a table can have. Normally, SQL Server has a hard limit of 1,024 columns, but there are apparently ways to work around that.
You can use XMLSpy to convert your XML schema to a database structure, but you will likely need to edit it further using DatabaseSpy to actually work with SQL Server for at least this reason.
If your schema is more complex than can be simply represented in a single table's row/column structure, XMLSpy may need to generate multiple, related tables to represent the structure.
XMLSpy can export your XML data files directly to a database, but this is intended more as a one-time operation, rather than something you can automate. For that, you're better off using MapForce to create a mapping between your XML schema (representing your XML data files) and the database structure you converted your schema to using XMLSpy.
The mapping will be set up to process all *.xml files (for example) in your input folder.
For more complete automation, you'll need to use MapForce Server to run your mapping from the command line or via API.
More complex workflow automation requirements (scheduling, triggering, etc.) may necessitate the use of FlowForce Server.
|
