We help IT Professionals succeed at work.

Text File with Multiple Headers into XML into SQL stored procedure how to??

anderdw2 used Ask the Experts™
Ok, a rather complex (for me at least) question to the experts.

We have a machine that exports data via a text file.  However, depending on the product that the machine is scanning, the data is different.  We have 15 product types and each product type can have upto 40 properties.  The next caveat, each property can have multiple values depending on the scan.  The first few header items are always the same.

So, the data could look something like this:  I made it short due to space.   In the example below, Hygro, Ph, and K are properties

ScanDate, Serial#, Product, Hygro, Hygro_Value, Hygro_ND, Hygro_Slope, Ph, Ph_Value, Ph_ND, Ph_Slope, K, K_Value, K_ND, K_Slope, ..............I could have 100+

So, my question(s)   What is my best method to get this into a sql server stored procedure?

If I create a class for each product type, my classes become enourmous.
Can I do a basic class for the base like Hygro, Ph, K, etc and then a sub (dynamic class) to add the nd, slope, value, etc?

Also, because I'm dealing with so many parameters, I hate to do sql parameters for every instance of these.  I would have to have 15 different sql calls with parameters numbering in the 100's   I've only every passed a handful of parameters.  So, from my searching I see that my best method is to create an xml and use the xml.nodes in my stored procedure.

Am I looking at this all wrong?  

I'm not even sure how to handle the multiple headers in a text file.  I'm still searching EE, but I need to make sure I'm on the right track, as my timeline is beyond short for this task.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®


Should I completely skip the class creation and just go from the txt to the xml?  

I'd like to create the class as well, as I know that will be used later down the road.  I'm still not sure the best approach for that as well.  I don't want a class with 100+ properties