I get a zipped upload of usually a couple hundred XML files and one index xml file (to reference all of the xml files included) daily. I need to create a procedure inside of SQL that will both parse the data, store it, and loop through all of the XML files (and then move all "completed" files to a different directory). If all of this can be cone inside SQL, perfect. If not, please propose your methodology. We natively are a ColdFusion environment.
There are really 4 parsing requirements...parse out the base layers of the xml (so things like name of product, sku, etc, there are only about 10 elements you have to look for here). If the product already exists, we will need to update the product data, if not, insert. Secondly, you have to insert category data into a different table ONLY if that category does not exist. If it does, you need to find that category ID and insert it into the product information table where the rest of the data went. Thirdly, there are product specs that need to be inserted into a spec table, that also have spec categories that will need to be cross referenced or stored. There are 3 different types of specs you'll need to be parse in the xml file. Finally, there are related products that will need to be placed in another table, and included in the base table if that information does not exist yet.
We've done this recently for a similar catalog (but with a different protocol) and I think it took about 10 hours of work, I'm guessing it will take a solid day, but not a whole lot more.
Please let me know if you have questions. Thanks!
38 freelancers are bidding on average $528 for this job
Hello. Need some clarification. I can use only C# for that (or VB.Net if you prefer it). Also need to know average size of each xml file with data. Not the size of index xml file. Thanks, Pavel.