Read XML based file that is recognized by Excel
 

I am having a problem reading an Excel file that is XML based through C#. The directory
I am reading contains Excel files that can be of two types. Either generic Microsoft
based or XML based. I am reading the Microsoft based files with an OleDbDataAdapter.
Then filling the contents of the first worksheet into a dataset.

However when I try to add the XML based file to my dataset using an XmlTextReader
I can never seem to get it to save to a different table in the dataset in the same
format as when I use an OleDbDataAdapter for the generic Excel files. If I save
it(using ds.ReadXml) to my existing DataSet (which already has one table in it)
and no table gets created.

I tried saving it to a brand new DataSet and saw that 10 tables were created. I assume
mostly all are these are style sheet information. The last table (entitled "data") seems
to have all my data. I then tried to do a copy of that table and place it in my existing
DataSet. However column header information is not captured and I can't read the
table because when I open up the DataSet at a (later point in my program) I retrieve
datarow elements by specifying column names and the OleDb connection complains
it can't find them.

Perhaps there is a tweak I need to the XmlTextReader or perhaps my XML based
Excel file? I was thinking worse case I would try to find some OS command to convert
the file to a real Microsoft based format but obviously it would be easier to tweak
the XmlTextReader or the file itself. Here is a snippet of the code along with a
sample of the XML text file. If anyone has any solutions, it would be greatly appreciated!!

OleDbConnection objConn = new OleDbConnection(fileConnectionString);
try
{
objConn.Open(); //<---- This will trigger an exception if the file is XML based
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + worksheetName + "$]", objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
objAdapter1.Fill(fileContents,filename);
}
catch
{
DataSet ds = new DataSet();
// Create new FileStream to read schema with.
System.IO.FileStream fsReadXml = new System.IO.FileStream (filename, System.IO.FileMode.Open);
// Create an XmlTextReader to read the file.
System.Xml.XmlTextReader myXmlReader = new System.Xml.XmlTextReader(fsReadXml);
// Read the XML document into the DataSet.
ds.ReadXml(myXmlReader, XmlReadMode.ReadSchema);
// Close the XmlTextReader
myXmlReader.Close();
DataTable xmlData = ds.Tables["data"].Copy();
xmlData.TableName = filename;
fileContents.Tables.Add(xmlData);
}
finally
{
objConn.Close();
}

<?xml version='1.0'?
<ss:Workbook xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'
<ss:Styles
<ss:Style ss:ID='1'
<ss:Font ss:Bold='1'/
</ss:Style
<ss:Style ss:ID="s22"
<ss:NumberFormat ss:Format="Short Date"/
</ss:Style
</ss:Styles
<ss:Worksheet ss:Name='Survey'
<ss:Table<ss:Column ss:Width='60'/
<ss:Column ss:Width='60'/
<ss:Column ss:Width='60'/
<ss:Column ss:Width='60'/
<ss:Column ss:Width='60'/
<ss:Column ss:Width='60'/
<ss:Column ss:Width='60'/
<ss:Column ss:Width='60'/
<ss:Row ss:StyleID='1'
<ss:Cell<ss:Data ss:Type='String'MEM_ID</ss:Data</ss:Cell
<ss:Cell<ss:Data ss:Type='String'CALL_DATE</ss:Data</ss:Cell
<ss:Cell<ss:Data ss:Type='String'SURVEY_FORM</ss:Data</ss:Cell
<ss:Cell<ss:Data ss:Type='String'SOURCE</ss:Data</ss:Cell
<ss:Cell<ss:Data ss:Type='String'REASON_CODE</ss:Data</ss:Cell
<ss:Cell<ss:Data ss:Type='String'ACTION_CODE</ss:Data</ss:Cell
<ss:Cell<ss:Data ss:Type='String'OPERATOR_ID</ss:Data</ss:Cell
<ss:Cell<ss:Data ss:Type='String'COMMENTS</ss:Data</ss:Cell
</ss:Row

<ss:Row
<ss:Cell<ss:Data ss:Type='String'555555555</ss:Data</ss:Cell
<ss:Cell ss:StyleID="s22"<ss:Data ss:Type='DateTime'2005-10-21T12:00:00.000</ss:Data</ss:Cell
<ss:Cell<ss:Data ss:Type='String'DNTL</ss:Data</ss:Cell
<ss:Cell<ss:Data ss:Type='String'MSPA</ss:Data</ss:Cell
<ss:Cell<ss:Data ss:Type='String'ee</ss:Data</ss:Cell
<ss:Cell<ss:Data ss:Type='String'ff</ss:Data</ss:Cell
<ss:Cell<ss:Data ss:Type='String'elmer_fudd</ss:Data</ss:Cell
<ss:Cell<ss:Data ss:Type='String'</ss:Data</ss:Cell
</ss:Row
</ss:Table </ss:Worksheet </ss:Workbook