XML to SQL; C#.NET 2.0, SQLServer2000, Relationship issues?

Hi there,< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

This will be my third time in attempting to post.  IE was clever enough to figure that when I opened my XML file I wanted it to open over my post, thus losing my work.  Bah.  At any rate, I am going to give as much info as possible, so bear with me.  There is a question here, its just wayyyy down.

At any rate, I am attempting to import XML into SQL Server 2000 using C#.NET 2.0.  I can read and navigate the XML with ease;

                XmlReaderSettings s = new XmlReaderSettings();

                s.ValidationType = ValidationType.Schema;

                XmlReader r = XmlReader.Create(new XmlTextReader(XMLPath), s);

                DataSet ds = new DataSet();

                ds.ReadXml(r);

                return ds;

Now lets get the data into my relational database.  Ok, so first I made a SqlDataAdapter and gave it some SQL that opened all the required tables.  Well, of course that didn’t work, as is outlined in many posts.  Ok, so the next thought is that I can use many data adapters to cover the import (simplified from x tables to 2 tables);

#region//And the commands to fill the adapters for the individual tables

            SqlCommand sqlCmdC2OrderHeader = new SqlCommand();

            sqlCmd = new SqlCommand();

            #endregion

            #region//Initialise the commands

            sqlCmd.CommandType = CommandType.Text;

            sqlCmd.Connection = sqlConn;

            sqlCmd.CommandText = "SELECT OrderNumber, StoreName " +

                "FROM C2Orders WHERE 1=2";

            sqlCmdC2OrderHeader.Connection = sqlConn;

            sqlCmdC2OrderHeader.CommandType = CommandType.Text;

            sqlCmdC2OrderHeader.CommandText = "SELECT OrderNumber, IP, " +

                "affiliateID, campaignID, orderStatus, invoiceType, " +

                "orderType, grandTotal, currency, shipCode, creationDate, " +

                "modifiedDate FROM C2OrderHeader WHERE 1=2";

            #endregion

            #region //Dataadapters to hold the commands and generate updates

            SqlDataAdapter sqlDAC2OrderHeader = new SqlDataAdapter();

            sqlDA = new SqlDataAdapter();

            #endregion

            #region //Make table mappings

            sqlDA.TableMappings.Add("C2Orders", "order");

            sqlDAC2OrderHeader.TableMappings.Add("C2OrderHeader", "orderHeader");

            #endregion

            #region //Set the select commands to the dataadapters

            sqlDA.SelectCommand = sqlCmd;

            sqlDAC2OrderHeader.SelectCommand = sqlCmdC2OrderHeader;

            #endregion

            #region //Attempt to fill schemas

            sqlDA.FillSchema(ds, SchemaType.Source);

            sqlDAC2OrderHeader.FillSchema(ds, SchemaType.Source);

            #endregion

            #region //Create update/insert commands for the adapters

            SqlCommandBuilder builder = new SqlCommandBuilder(sqlDA);

            //sqlDA.MissingMappingAction = MissingMappingAction.Ignore;

            sqlDA.MissingMappingAction = MissingMappingAction.Passthrough;

            sqlDA.UpdateCommand = builder.GetUpdateCommand();

            sqlDA.InsertCommand = builder.GetInsertCommand();

            SqlCommandBuilder builder3 = new SqlCommandBuilder(sqlDAC2OrderHeader);

            //sqlDAC2OrderHeader.MissingMappingAction = MissingMappingAction.Ignore;

            sqlDAC2OrderHeader.MissingMappingAction = MissingMappingAction.Passthrough;

            sqlDAC2OrderHeader.UpdateCommand = builder3.GetUpdateCommand();

            sqlDAC2OrderHeader.InsertCommand = builder3.GetInsertCommand();

            #endregion

            #region //Finally, execute

            sqlDA.Update(ds, "C2Orders");

            sqlDAC2OrderHeader.Update(ds, "C2OrderHeader");

            #endregion

Ok, this didn’t work.  The reason given, “Missing the DataColumn 'OrderNumber' in the DataTable 'orderHeader' for the SourceColumn 'OrderNumber'.”  I figure this is due to the fact that the xml “Child” node does not have an order number.  Here I was hoping that something would recognize that it was needed.  The parent is the one that has the Order Number;

              < xml version="1.0" encoding="UTF-8" >

<orders>

<order>

  <orderNumber>123456</orderNumber>

  <storeName>SomeStore</storeName>

   <orderHeader> 

      <affiliateID />

      <campaignID />

      <IP>111.111.111.111</IP>

      <orderStatus>SomeStatus</orderStatus>

      <invoiceType>SomeInvoice</invoiceType>

      <orderType>SomeType</orderType>

      <grandTotal>99.99</grandTotal>

      <currency>CAD</currency>

      <shipCode>SomeCode</shipCode>

      <creationDate>2007-01-19T06:50:49</creationDate>

      <modifiedDate>2007-01-19T06:48:07</modifiedDate>

</orderHeader>

</order>

</orders>

Allright, I tried something else that is outside the scope of this particular forum thread, with SQLXMLBULKLOADLib.  It seemed to be kind of nifty.  Created the annotated schema, but had similar errors;

SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class bl = new SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class();

            bl.ConnectionString = "PROVIDER=SQLOLEDB.1;SERVER=IT-004;UID=user;PWD=pass;DATABASE=IMS20";

            bl.KeepIdentity = false;

            bl.Transaction = false;

            bl.ErrorLogFile = @".\Screwed.txt";

            bl.Execute(@".\AnnotatedSCHEMA.xsd", @".\ORDERS011120071540.xml");

Because this method is out of scope, I’ll not get into it.  Instead I figure I will summarise and ask my real question, finally.  How do I tell my dataset that it should look for order number in the parent node C2Orders (order) instead of its own C2OrderHeader (orderHeader) section

By the code pasted above, you’ve probably said to yourself “This guy doesn’t know what the hell he is doing” or “This guy doesn’t know what he is even trying to do!”.  Well, if that is the case, enlighten me!  Are these approaches what you would do to import XML   What if I wanted to UPDATE modifiedDate for orders that were already in the SQL server and insert everything else   Is the dataset flexible enough to do this

For anyone still awake, thanks for reading this far!

Dylan