Data Import - Your thoughts appreciated

I have a database with a dozen or so tables. No table constraints. Logic is all in stored procedures.

I have several Excel spreadsheets of data to import into the database, one speadsheet to a table. Each spreadsheet has additional data(columns) that each table has no interest in and should be ignored.

I would appreciate your thoughts on methods and best practices for loading this data to the database.

I am about to investigate SQL Server 2005 Express handling of XML. I am familiar with XML and XSL conversions and it seems to me that XSL conversion of Excel data to XML gives me a lot of flexibility prior to database import for shaping the data.

In short, importing data to the database from an XML source.

I am not famliar with SQL Server's XML capability and would appreciate thoughts on this while I look into it.

And of course alternate ways that I am overlooking.

Thanks

Check in Books Online for Topics:

OpenXML Bulk Load [SQL Server]|||

If you would like ti import Excel speadsheets as XML data inside a database table, you will have to convert those spreadsheets to XML documents/formats first before you can even import them. Plus, you need to create XSDs - XML Schema Definitions - for those XML to make sure you are only importing valid XML. Here's a sample code for using the INSERT stament to insert XML data in an XML column in SQL Server 2005 - docs being your table name.

INSERT INTO docs VALUES (2,
'<doc id="123">
<sections>
<section num="1"><title>XML Schema</title></section>
<section num="3"><title>Benefits</title></section>
<section num="4"><title>Features</title></section>
</sections>
</doc>')