XML case: automated export with dynamic schema
 

Hi all, not sure this is right newsgroup for my questions.

I'd like to provide my users with Excel documents (simple lists with column
headers).
Then, I'd like to upload this excel document into SQL Server, in a
normalized model
The excel has a not-predictable layout, except that column headers will be
within a predefined set of possibilities and there will always be a field
CODE.

CODE LONG_DESC SHORT_DESC DUNS ...
1 LONGa SHORTa DUNSa
2 LONGb SHORTb DUNSb
3 LONGc SHORTc DUNSc
-- upload into data model
CODE
FIELD (e.g. LONG_DESC,SHORT_DESC, DUNS...)
VALUE (e.g. LONGa, SHORTa, DUNSa, LONGb, SHORTb, DUNSb, LONGc, SHORTc,
DUNSc...)

My idea is to use XML:
- export excel to XML: this will be denormalized (lots of troubles with this)
- normalize this XML with XSLT to CODE, FIELD, VALUE elements (I have
covered this)
- upload to SQL Server with OPEN XML (I have covered this)

My main problem is to automate the process of exporting to XML from an Excel
file.
Different parts of the problem, as far as I got:
1. I need to be able to generically create an XML map for exporting purposes
2. this must run stand-alone: the Excel-sheets (no macros in the sheet) will
be dropped in a directory and then the process must start.

Maybe, I could make a long XSD with all possible fields, but then I must map
the columns with the corresponding XML elements.
The column order in the Excel sheet might be different than the element
order in the XML map.

Anyone did something comparably, or can point me towards some reference that
would help me?

Thanks in advance.
Bert