Earlier this year I had the need to do the initial data load for a new system. The data source was a highly complex and customized Excel spreadsheet that provided no way to easily automate the migration.
Manual processing it’s definitely not my cup of tea, so I decided that at least the result should be something much more manageable which immediately ruled out creating discrete SQL insert commands, given the hierarchical and highly relational nature of the data.
XML seemed like the way to go, but then I would need a custom application to interpret it and finally load it to the database. Fortunately Microsoft did just that with SQLXML, providing a very flexible way to load XML data into a relational database.
Being a console guy I ended up creating a quick console application that enabled me to more easily automate the process. The first version of this application contained almost all SQL XML configuration hardcoded, but recently I had some time and improved it with command line parameters for the available configuration. Try it here.