How to read an XML File into a single DataTable C#

Question

I have an XML file.

When I open it with Excel; I get a popup asking me:

"Please select how you would like to open this file: "

Then there are 3 options of which the first one is selected by default. That option is:

"As an XML table"

I then leave the default ticked and click: "OK" and I get 1 sheet of data (The specific XML I'm using has a lot of columns in the 1 sheet of data in the excel).

I tried to read that same XML file in C# into a DataSet using this code:

DataSet theMainXMLData = new DataSet();
theMainXMLData.ReadXml(fullFilePath);

The problem is; that DataSet has multiple DataTables in it. I need it all in 1 DataTable the way Excel had it all in 1 sheet.

I've tried using the overload of that method: ReadXml using different XmlReadMode's, but none that I've tried gives me all the data in 1 DataTable.

How can I get all my data in 1 DataTable?

How does Excel do it?

Here's a test sample of the XML I'm trying to read:

<?xml version="1.0" encoding="utf-8"?>
<SupplierDataReport>
  <Header>
    <Note>TEST</Note>
    <UserName>UserName TEST</UserName>
    <DateDrawn>01 Jan 1999</DateDrawn>
    <Group>Group TEST</Group>
    <Branch>Branch TEST</Branch>
    <Product>Product TEST</Product>
    <Administrator>Administrator TEST</Administrator>
    <Claims>Claims TEST</Claims>
    <Owner>Owner TEST</Owner>
    <Underwriter>Underwriter TEST</Underwriter>
    <StartDate>01 Jan 1999</StartDate>
    <EndDate>02 Jan 1999</EndDate>
    <DeclinedTransactions>DeclinedTransactions TEST</DeclinedTransactions>
    <DraftMode>DraftMode TEST</DraftMode>
    <DraftMessage>DraftMessage TEST</DraftMessage>
    <NumberofRecords>1</NumberofRecords>
  </Header>
  <Products>
    <ProductDetails>
      <Transaction>
        <PolicyId>PolicyId TEST</PolicyId>
        <PolicyNumber>PolicyNumber1</PolicyNumber>
        <InceptionDate>01-Jan-1999</InceptionDate>
        <GroupId>GroupId TEST</GroupId>
        <GroupCode>GroupCode TEST</GroupCode>
        <GroupName>GroupName TEST</GroupName>
        <BranchId>BranchId TEST</BranchId>
        <BranchCode>BranchCode TEST</BranchCode>
        <BranchName>BranchName TEST</BranchName>
        <BranchPOAddressLine1>BranchPOAddressLine1 TEST</BranchPOAddressLine1>
        <BranchPOAddressLine2>
        </BranchPOAddressLine2>
        <BranchPOAddressSuburb>BranchPOAddressSuburb TEST</BranchPOAddressSuburb>
        <BranchPOAddressCity>BranchPOAddressCity TEST</BranchPOAddressCity>
        <BranchPOAddressPostCode>BranchPOAddressPostCode TEST</BranchPOAddressPostCode>
        <BranchPHAddressLine1>BranchPHAddressLine1 TEST</BranchPHAddressLine1>
        <BranchPHAddressLine2>
        </BranchPHAddressLine2>
        <BranchPHAddressSuburb>BranchPHAddressSuburb TEST</BranchPHAddressSuburb>
        <BranchPHAddressCity>BranchPHAddressCity TEST</BranchPHAddressCity>
        <BranchPHAddressPostCode>BranchPHAddressPostCode TEST</BranchPHAddressPostCode>
        <BranchTelephoneCode>BranchTelephoneCode TEST</BranchTelephoneCode>
        <BranchTelephoneNumber>BranchTelephoneNumber TEST</BranchTelephoneNumber>
        <BranchFaxCode>BranchFaxCode TEST</BranchFaxCode>
        <BranchFaxNumber>BranchFaxNumber TEST</BranchFaxNumber>
        <docFinanceCompanyCode>docFinanceCompanyCode TEST</docFinanceCompanyCode>
        <docFinanceCompanyName>docFinanceCompanyName TEST</docFinanceCompanyName>
        <docFinanceAccountNumber>docFinanceAccountNumber TEST</docFinanceAccountNumber>
        <docInsuranceCompanyCode>
        </docInsuranceCompanyCode>
        <docInsuranceCompanyName>
        </docInsuranceCompanyName>
        <docInsuranceAccountNumber>
        </docInsuranceAccountNumber>
        <DepositValue>0.00</DepositValue>
        <FinanceAmount>0.00</FinanceAmount>
        <ResidualValue>
        </ResidualValue>
        <BusinessManagerId>BusinessManagerId TEST</BusinessManagerId>
        <BusinessManager>BusinessManager TEST</BusinessManager>
        <BMWorkTelephone>BMWorkTelephone TEST</BMWorkTelephone>
        <BMMobileNumber>BMMobileNumber TEST</BMMobileNumber>
        <BMEmailAddress>BMEmailAddress TEST</BMEmailAddress>
        <Notes>
        </Notes>
        <FinanceTerm>FinanceTerm TEST</FinanceTerm>
        <SalesPersonId>SalesPersonId TEST</SalesPersonId>
        <SalesPerson>SalesPerson TEST</SalesPerson>
        <InterestRateType>InterestRateType TEST</InterestRateType>
        <InterestRate>0.0</InterestRate>
        <DigitallySigned>DigitallySigned TEST</DigitallySigned>
        <CompanyConsent>CompanyConsent TEST</CompanyConsent>
        <OtherCompanyConsent>OtherCompanyConsent TEST</OtherCompanyConsent>
        <MarketingConsent>MarketingConsent TEST</MarketingConsent>
        <LegitimateInterestConsent>LegitimateInterestConsent TEST</LegitimateInterestConsent>
        <FinancePromotionCode>
        </FinancePromotionCode>
        <IncludedSchedule>
        </IncludedSchedule>
        <MedicalAidScheme>
        </MedicalAidScheme>
        <MedicalAidNumber>
        </MedicalAidNumber>
        <EmergencyContactName1>
        </EmergencyContactName1>
        <EmergencyContactNumber1>
        </EmergencyContactNumber1>
        <EmergencyContactName2>
        </EmergencyContactName2>
        <EmergencyContactNumber2>
        </EmergencyContactNumber2>
        <CashTransaction>CashTransaction TEST</CashTransaction>
        <finContractStartDate>
        </finContractStartDate>
        <finFirstDebitDate>
        </finFirstDebitDate>
        <POPIConcent>POPIConcent TEST</POPIConcent>
        <VehicleUse>VehicleUse TEST</VehicleUse>
        <LatestReferenceNumber>LatestReferenceNumber TEST</LatestReferenceNumber>
        <LatestAlternativeReferenceNumber>LatestAlternativeReferenceNumber TEST</LatestAlternativeReferenceNumber>
        <LatestAccountNumber>LatestAccountNumber TEST</LatestAccountNumber>
        <DealerOwnerCode>
        </DealerOwnerCode>
        <PackageCode>
        </PackageCode>
        <FspCompanyName>FspCompanyName TEST</FspCompanyName>
        <FspCompanyNumber>FspCompanyNumber TEST</FspCompanyNumber>
        <GroupBranchRegistrationNumber>GroupBranchRegistrationNumber TEST</GroupBranchRegistrationNumber>
      </Transaction>
      <Client>
        <ClientCategory>ClientCategory TEST</ClientCategory>
        <ClientTitle>ClientTitle TEST</ClientTitle>
        <ClientFirstName>ClientFirstName TEST</ClientFirstName>
        <ClientLastName>ClientLastName TEST</ClientLastName>
        <ClientIDType>ClientIDType TEST</ClientIDType>
        <ClientIDNumber>ClientIDNumber TEST</ClientIDNumber>
        <ClientGender>ClientGender TEST</ClientGender>
        <ClientMobileNumber>ClientMobileNumber TEST</ClientMobileNumber>
        <ClientWorkTelephoneCode>ClientWorkTelephoneCode TEST</ClientWorkTelephoneCode>
        <ClientWorkTelephoneNumber>ClientWorkTelephoneNumber TEST</ClientWorkTelephoneNumber>
        <ClientHomeTelephoneCode>
        </ClientHomeTelephoneCode>
        <ClientHomeTelephoneNumber>
        </ClientHomeTelephoneNumber>
        <ClientEmailAddress>ClientEmailAddress TEST</ClientEmailAddress>
        <ClientOccupationName>ClientOccupationName TEST</ClientOccupationName>
        <ClientPOAddressLine1>ClientPOAddressLine1 TEST</ClientPOAddressLine1>
        <ClientPOAddressLine2>ClientPOAddressLine2 TEST</ClientPOAddressLine2>
        <ClientPOAddressSuburb>ClientPOAddressSuburb TEST</ClientPOAddressSuburb>
        <ClientPOAddressCity>ClientPOAddressCity TEST</ClientPOAddressCity>
        <ClientPOAddressPostCode>ClientPOAddressPostCode TEST</ClientPOAddressPostCode>
        <ClientPOAddressProvinceName>ClientPOAddressProvinceName TEST</ClientPOAddressProvinceName>
        <ClientPOAddressCountryName>ClientPOAddressCountryName TEST</ClientPOAddressCountryName>
        <ClientPHAddressLine1>ClientPHAddressLine1 TEST</ClientPHAddressLine1>
        <ClientPHAddressLine2>ClientPHAddressLine2 TEST</ClientPHAddressLine2>
        <ClientPHAddressSuburb>ClientPHAddressSuburb TEST</ClientPHAddressSuburb>
        <ClientPHAddressCity>ClientPHAddressCity TEST</ClientPHAddressCity>
        <ClientPHAddressPostCode>ClientPHAddressPostCode TEST</ClientPHAddressPostCode>
        <ClientPHAddressProvinceName>ClientPHAddressProvinceName TEST</ClientPHAddressProvinceName>
        <ClientPHAddressCountryName>ClientPHAddressCountryName TEST</ClientPHAddressCountryName>
        <MaritalStatus>MaritalStatus TEST</MaritalStatus>
        <ClientEmploymentType>ClientEmploymentType TEST</ClientEmploymentType>
        <ClientPassportIssueDate>
        </ClientPassportIssueDate>
        <ClientPassportExpiryDate>
        </ClientPassportExpiryDate>
        <ClientBirthDate>01-Jan-1999</ClientBirthDate>
        <ContactMethod>ContactMethod TEST</ContactMethod>
      </Client>
      <Vehicle>
        <StockNumber>StockNumber TEST</StockNumber>
        <MMCode>MMCode TEST</MMCode>
        <Manufacturer>Manufacturer TEST</Manufacturer>
        <Model>Model TEST</Model>
        <NewUsed>NewUsed TEST</NewUsed>
        <FirstRegistrationDate>01-Jan-1999</FirstRegistrationDate>
        <RegistrationNumber>RegistrationNumber TEST</RegistrationNumber>
        <VINNumber>VINNumber TEST</VINNumber>
        <EngineNumber>EngineNumber TEST</EngineNumber>
        <OdometerReading>0</OdometerReading>
        <RetailPrice>0.00</RetailPrice>
        <DiscountAmount>0.00</DiscountAmount>
        <RegistrationFee>
        </RegistrationFee>
        <DeliveryFee>
        </DeliveryFee>
        <Accessories>Accessories TEST</Accessories>
        <AccessoryTotal>0.00</AccessoryTotal>
        <VehicleValue>0.00</VehicleValue>
        <InspectorName>
        </InspectorName>
        <VehicleDamage>
        </VehicleDamage>
        <LeftFrontFenderDamage>
        </LeftFrontFenderDamage>
        <LeftFrontDoorDamage>
        </LeftFrontDoorDamage>
        <LeftBackFenderDamage>
        </LeftBackFenderDamage>
        <LeftBackDoorDamage>
        </LeftBackDoorDamage>
        <RightFrontFenderDamage>
        </RightFrontFenderDamage>
        <RightFrontDoorDamage>
        </RightFrontDoorDamage>
        <RightBackFenderDamage>
        </RightBackFenderDamage>
        <RightBackDoorDamage>
        </RightBackDoorDamage>
        <BonnetDamage>
        </BonnetDamage>
        <RoofDamage>
        </RoofDamage>
        <BootDamage>
        </BootDamage>
        <FrontBumperDamage>
        </FrontBumperDamage>
        <BackBumperDamage>
        </BackBumperDamage>
        <FullServiceHistory>NO</FullServiceHistory>
      </Vehicle>
      <Product>
        <ProductId>ProductId TEST</ProductId>
        <ProductName>ProductName TEST</ProductName>
        <ProductOptionId>ProductOptionId TEST</ProductOptionId>
        <ProductOptionCode>ProductOptionCode TEST</ProductOptionCode>
        <ProductOptionName>ProductOptionName TEST</ProductOptionName>
        <ProductAmount>0.00</ProductAmount>
        <ProductVATAmount>0.00</ProductVATAmount>
        <ProductTotalAmount>0.00</ProductTotalAmount>
        <PayoverAmount>0.00</PayoverAmount>
        <PayoverVATAmount>0.00</PayoverVATAmount>
        <PayoverTotalAmount>0.00</PayoverTotalAmount>
        <CommissionAmount>
        </CommissionAmount>
        <CollectionFee>
        </CollectionFee>
        <PaymentType>PaymentType TEST</PaymentType>
        <MonthlyPremium>
        </MonthlyPremium>
        <CoverAmount>
        </CoverAmount>
        <Term>
        </Term>
        <ExpiryDate>
        </ExpiryDate>
        <mtnExpiryKilometres>
        </mtnExpiryKilometres>
        <mtnManufacturerPlanType>
        </mtnManufacturerPlanType>
        <mtnManufacturerExpiryMonths>
        </mtnManufacturerExpiryMonths>
        <mtnManufacturerExpiryKilometres>
        </mtnManufacturerExpiryKilometres>
        <warAdditionalMonths>
        </warAdditionalMonths>
        <warManufacturerExpiryMonths>
        </warManufacturerExpiryMonths>
        <SIFirstName>
        </SIFirstName>
        <SILastName>
        </SILastName>
        <SIIDType>
        </SIIDType>
        <SIIDNumber>
        </SIIDNumber>
        <SIGender>
        </SIGender>
        <SIMobileNumber>
        </SIMobileNumber>
        <SIWorkTelephoneCode>
        </SIWorkTelephoneCode>
        <SIWorkTelephoneNumber>
        </SIWorkTelephoneNumber>
        <SIHomeTelephoneCode>
        </SIHomeTelephoneCode>
        <SIHomeTelephoneNumber>
        </SIHomeTelephoneNumber>
        <SIEmailAddress>
        </SIEmailAddress>
        <SIAddressLine1>
        </SIAddressLine1>
        <SIAddressLine2>
        </SIAddressLine2>
        <SIAddressSuburb>
        </SIAddressSuburb>
        <SIAddressCity>
        </SIAddressCity>
        <SIAddressPostCode>
        </SIAddressPostCode>
        <SIAddressProvinceName>
        </SIAddressProvinceName>
        <SIAddressCountryName>
        </SIAddressCountryName>
        <FirstDebitDate>
        </FirstDebitDate>
        <FirstDebitAmount>
        </FirstDebitAmount>
        <RecurringDebitDay>
        </RecurringDebitDay>
        <RecurringDebitAmount>
        </RecurringDebitAmount>
        <CollectionAgent>
        </CollectionAgent>
        <ProductPolicyFee>0.00</ProductPolicyFee>
        <ReferenceNo>
        </ReferenceNo>
        <ProductInspectionFee>0.00</ProductInspectionFee>
        <mtnExistingPlanType>
        </mtnExistingPlanType>
        <mtnExistingExpiryDate>
        </mtnExistingExpiryDate>
        <mtnExistingExipryKilometrs>
        </mtnExistingExipryKilometrs>
        <warManufacturePlan>
        </warManufacturePlan>
        <warManufacturerExpiryKilometres>
        </warManufacturerExpiryKilometres>
        <warExistingPlan>
        </warExistingPlan>
        <warExistingExpiryDate>
        </warExistingExpiryDate>
        <warExistingExpiryKilometres>
        </warExistingExpiryKilometres>
        <warAdditionalKilometres>
        </warAdditionalKilometres>
        <warExpiryKilometres>
        </warExpiryKilometres>
        <SITitle>
        </SITitle>
        <SIRelationship>
        </SIRelationship>
        <ProductTypeCatergoryCode>ProductTypeCatergoryCode TEST</ProductTypeCatergoryCode>
        <ProductTypeCatergoryName>ProductTypeCatergoryName TEST</ProductTypeCatergoryName>
        <ProductOwner>ProductOwner TEST</ProductOwner>
        <ProductAdministrator>ProductAdministrator TEST</ProductAdministrator>
        <SIBirthDate>
        </SIBirthDate>
        <ProductClaimsCompany>ProductClaimsCompany TEST</ProductClaimsCompany>
        <ProductUnderwriterCompany>ProductUnderwriterCompany TEST</ProductUnderwriterCompany>
        <ProductAdminFee>0.00</ProductAdminFee>
        <BinderFee>0.00</BinderFee>
        <DealerDocumentationFee>0.00</DealerDocumentationFee>
        <ValuationFee>0.00</ValuationFee>
        <ProductNote>
        </ProductNote>
        <SupplierRecovery>0.00</SupplierRecovery>
        <BillToCompanyName>
        </BillToCompanyName>
        <OptionDisclosure>
        </OptionDisclosure>
        <OptionQuoteNumber>
        </OptionQuoteNumber>
        <Commencement>01-Jan-1999</Commencement>
        <mtnKilometresCommencement>
        </mtnKilometresCommencement>
        <mtnAdditionalKilometres>
        </mtnAdditionalKilometres>
        <BeneficiaryFirstName>
        </BeneficiaryFirstName>
        <BeneficiaryLastName>
        </BeneficiaryLastName>
        <BeneficiaryIDType>
        </BeneficiaryIDType>
        <BeneficiaryIDNumber>
        </BeneficiaryIDNumber>
        <BeneficiaryMobileNumber>
        </BeneficiaryMobileNumber>
        <BeneficiaryWorkTelephoneCode>
        </BeneficiaryWorkTelephoneCode>
        <BeneficiaryWorkTelephoneNumber>
        </BeneficiaryWorkTelephoneNumber>
        <BeneficiaryHomeTelephoneCode>
        </BeneficiaryHomeTelephoneCode>
        <BeneficiaryHomeTelephoneNumber>
        </BeneficiaryHomeTelephoneNumber>
        <BeneficiaryRelationship>
        </BeneficiaryRelationship>
      </Product>
      <BankingDetails>
        <BankName>
        </BankName>
        <BankBranchName>
        </BankBranchName>
        <BankBranchCode>
        </BankBranchCode>
        <BankAccountType>
        </BankAccountType>
        <BankAccountNumber>
        </BankAccountNumber>
        <AccountHolderName>
        </AccountHolderName>
      </BankingDetails>
      <Company>
        <CompanyName>
        </CompanyName>
        <CompanyRegistrationNumber>
        </CompanyRegistrationNumber>
        <CompanyVATNumber>
        </CompanyVATNumber>
        <CompanyAddressLine1>
        </CompanyAddressLine1>
        <CompanyAddressLine2>
        </CompanyAddressLine2>
        <CompanyAddressSuburb>
        </CompanyAddressSuburb>
        <CompanyAddressCity>
        </CompanyAddressCity>
        <CompanyAddressPostCode>
        </CompanyAddressPostCode>
        <CompanyAddressProvinceName>
        </CompanyAddressProvinceName>
        <CompanyAddressCountryName>
        </CompanyAddressCountryName>
      </Company>
      <VehicleTyre1>
        <BatchNumber>
        </BatchNumber>
      </VehicleTyre1>
      <VehicleTyre2>
        <BatchNumber>
        </BatchNumber>
      </VehicleTyre2>
      <VehicleTyre3>
        <BatchNumber>
        </BatchNumber>
      </VehicleTyre3>
      <VehicleTyre4>
        <BatchNumber>
        </BatchNumber>
      </VehicleTyre4>
      <VehicleTyre5>
        <BatchNumber>
        </BatchNumber>
      </VehicleTyre5>
    </ProductDetails>
  </Products>
</SupplierDataReport>

Any help would be appreciated.

Post sample of xml. When xml has too many descendants you cannot use the DataSet ReadXml() method which will create a fragmented DataSet that is useless. — jdweng
– jdweng, Commented Apr 23, 2019 at 13:19
I can't post the XML that I'm trying, but I'll try create a sample and then post that. What other methods / code can I try that does not fragment my DataSet. @jdweng — Shiasu-sama
– Shiasu-sama, Commented Apr 23, 2019 at 14:40
A DataSet contains following tags 1) DataSet Name 2) Table(s) Names 3) Column Names 4) Row Data Once you get more than four levels of tags you get a fragmented mess. — jdweng
– jdweng, Commented Apr 23, 2019 at 14:52
@jdweng I've edited my question and added an XML Sample. Any help would be greatly appreciated. :). — Shiasu-sama
– Shiasu-sama, Commented Apr 25, 2019 at 6:18

jdweng · Accepted Answer · 2019-04-25 10:27:48Z

There is no easy way to do this. Best way is to serialize data. Most people create a schema and there are tools to convert the schema to c# classes. Without schema you have to create classes manually.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Serialization;


namespace ConsoleApplication110
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.xml";
        static void Main(string[] args)
        {
            XmlReader reader = XmlReader.Create(FILENAME);
            XmlSerializer serializer = new XmlSerializer(typeof(SupplierDataReport));
            SupplierDataReport report = (SupplierDataReport)serializer.Deserialize(reader);
        }

    }
    public class SupplierDataReport
    {
        public Header Header { get;set;}
        [XmlArray("Products")]
        [XmlArrayItem("ProductDetails")]
        public List<ProductDetails> ProductDetails { get; set; }
    }
    public class Header
    {
        public string Note { get;set;}
        public string UserName { get;set;}
        public string DateDrawn { get;set;}
        public string Group { get;set;}
        public string Branch { get;set;}
        public string Product { get;set;}
        public string Administrator { get;set;}
        public string Claims { get;set;}
        public string Owner { get;set;}
        public string Underwriter { get;set;}
        private DateTime _StartDate { get; set; }
        public string  StartDate {
            get { return _StartDate.ToString("dd MMM yyyy"); }
            set { _StartDate = DateTime.ParseExact(value, "dd MMM yyyy", System.Globalization.CultureInfo.InvariantCulture);}
        }
        private DateTime _EndDate { get; set; }
        public string EndDate
        {
            get { return _EndDate.ToString("dd MMM yyyy"); }
            set { _EndDate = DateTime.ParseExact(value, "dd MMM yyyy", System.Globalization.CultureInfo.InvariantCulture); }
        }
        public string DeclinedTransactions { get; set; }
        public string DraftMode { get; set; }
        public string DraftMessage { get; set; }
        public int NumberofRecords { get; set; }
    }
    public class ProductDetails
    {
        public Transaction Transaction { get; set; }
        public Client Client { get; set; }
        public Vehicle Vehicle { get; set; }
        public Product Product { get; set; }
        public BankingDetails BankingDetails { get; set; }
        public Company Company { get; set; }
        public cBatchNumber VehicleTyre1 { get; set; }
        public cBatchNumber VehicleTyre2 { get; set; }
        public cBatchNumber VehicleTyre3 { get; set; }
        public cBatchNumber VehicleTyre4 { get; set; }
        public cBatchNumber VehicleTyre5 { get; set; }
    }
    public class Transaction
    {
        public string PolicyId { get; set; }
        public string PolicyNumber { get; set; }

        private DateTime _InceptionDate { get; set; }
        public string InceptionDate
        {
            get { return _InceptionDate.ToString("dd-MMM-yyyy"); }
            set { _InceptionDate = DateTime.ParseExact(value, "dd-MMM-yyyy", System.Globalization.CultureInfo.InvariantCulture); }
        }
        public string GroupId { get; set; }
        public string GroupCode { get; set; }
        public string GroupName { get; set; }
        public string BranchId { get; set; }
        public string BranchCode { get; set; }
        public string BranchName { get; set; }
        public string BranchPOAddressLine1 { get; set; }
        public string BranchPOAddressLine2 { get; set; }
        public string BranchPOAddressSuburb { get; set; }
        public string BranchPOAddressCity { get; set; }
        public string BranchPOAddressPostCode { get; set; }
        public string BranchPHAddressLine1 { get; set; }
        public string BranchPHAddressLine2 { get; set; }
        public string BranchPHAddressSuburb { get; set; }
        public string BranchPHAddressCity { get; set; }
        public string BranchPHAddressPostCode { get; set; }
        public string BranchTelephoneCode { get; set; }
        public string BranchTelephoneNumber { get; set; }
        public string BranchFaxCode { get; set; }
        public string BranchFaxNumber { get; set; }
        public string docFinanceCompanyCode { get; set; }
        public string docFinanceCompanyName { get; set; }
        public string docFinanceAccountNumber { get; set; }
        public string docInsuranceCompanyCode { get; set; }
        public string docInsuranceCompanyName { get; set; }
        public string docInsuranceAccountNumber { get; set; }
        public decimal DepositValue { get; set; }
        public decimal FinanceAmount { get; set; }
        public string ResidualValue { get; set; }
        public string BusinessManagerId { get; set; }
        public string BusinessManager { get; set; }
        public string BMWorkTelephone { get; set; }
        public string BMMobileNumber { get; set; }
        public string BMEmailAddress { get; set; }
        public string Notes { get; set; }
        public string FinanceTerm { get; set; }
        public string SalesPersonId { get; set; }
        public string SalesPerson { get; set; }
        public string InterestRateType { get; set; }
        public decimal InterestRate { get; set; }
        public string DigitallySigned { get; set; }
        public string CompanyConsent { get; set; }
        public string OtherCompanyConsent { get; set; }
        public string MarketingConsent { get; set; }
        public string LegitimateInterestConsent { get; set; }
        public string FinancePromotionCode { get; set; }
        public string IncludedSchedule { get; set; }
        public string MedicalAidScheme { get; set; }
        public string MedicalAidNumber { get; set; }
        public string EmergencyContactName1 { get; set; }
        public string EmergencyContactNumber1 { get; set; }
        public string EmergencyContactName2 { get; set; }
        public string EmergencyContactNumber2 { get; set; }
        public string CashTransaction { get; set; }
        public string finContractStartDate { get; set; }
        public string finFirstDebitDate { get; set; }
        public string POPIConcent { get; set; }
        public string VehicleUse { get; set; }
        public string LatestReferenceNumber { get; set; }
        public string LatestAlternativeReferenceNumber { get; set; }
        public string LatestAccountNumber { get; set; }
        public string DealerOwnerCode { get; set; }
        public string PackageCode { get; set; }
        public string FspCompanyName { get; set; }
        public string FspCompanyNumber { get; set; }
        public string GroupBranchRegistrationNumber { get; set; }
    }
    public class Client
    {
        public string ClientCategory { get; set; }
        public string ClientTitle { get; set; }
        public string ClientFirstName { get; set; }
        public string ClientLastName { get; set; }
        public string ClientIDType { get; set; }
        public string ClientIDNumber { get; set; }
        public string ClientGender { get; set; }
        public string ClientMobileNumber { get; set; }
        public string ClientWorkTelephoneCode { get; set; }
        public string ClientWorkTelephoneNumber { get; set; }
        public string ClientHomeTelephoneCode { get; set; }
        public string ClientHomeTelephoneNumber { get; set; }
        public string ClientEmailAddress { get; set; }
        public string ClientOccupationName { get; set; }
        public string ClientPOAddressLine1 { get; set; }
        public string ClientPOAddressLine2 { get; set; }
        public string ClientPOAddressSuburb { get; set; }
        public string ClientPOAddressCity { get; set; }
        public string ClientPOAddressPostCode { get; set; }
        public string ClientPOAddressProvinceName { get; set; }
        public string ClientPOAddressCountryName { get; set; }
        public string ClientPHAddressLine1 { get; set; }
        public string ClientPHAddressLine2 { get; set; }
        public string ClientPHAddressSuburb { get; set; }
        public string ClientPHAddressCity { get; set; }
        public string ClientPHAddressPostCode { get; set; }
        public string ClientPHAddressProvinceName { get; set; }
        public string ClientPHAddressCountryName { get; set; }
        public string MaritalStatus { get; set; }
        public string ClientEmploymentType { get; set; }
        public string ClientPassportIssueDate { get; set; }
        public string ClientPassportExpiryDate { get; set; }
        private DateTime _ClientBirthDate { get; set; }
        public string ClientBirthDate
        {
            get { return _ClientBirthDate.ToString("dd-MMM-yyyy"); }
            set { _ClientBirthDate = DateTime.ParseExact(value, "dd-MMM-yyyy", System.Globalization.CultureInfo.InvariantCulture); }
        }
        public string ContactMethod { get; set; }
    }
    public class Vehicle
    {
        public string StockNumber { get; set; }
        public string MMCode { get; set; }
        public string Manufacturer { get; set; }
        public string Model { get; set; }
        public string NewUsed { get; set; }
        private DateTime _FirstRegistrationDate { get; set; }
        public string FirstRegistrationDate
        {
            get { return _FirstRegistrationDate.ToString("dd-MMM-yyyy"); }
            set { _FirstRegistrationDate = DateTime.ParseExact(value, "dd-MMM-yyyy", System.Globalization.CultureInfo.InvariantCulture); }
        }
        public string RegistrationNumber { get; set; }
        public string VINNumber { get; set; }
        public string EngineNumber { get; set; }
        public int OdometerReading { get; set; }
        public decimal RetailPrice { get; set; }
        public decimal DiscountAmount { get; set; }
        public string RegistrationFee { get; set; }
        public string DeliveryFee { get; set; }
        public string Accessories { get; set; }
        public decimal AccessoryTotal { get; set; }
        public decimal VehicleValue { get; set; }
        public string InspectorName { get; set; }
        public string VehicleDamage { get; set; }
        public string LeftFrontFenderDamage { get; set; }
        public string LeftFrontDoorDamage { get; set; }
        public string LeftBackFenderDamage { get; set; }
        public string LeftBackDoorDamage { get; set; }
        public string RightFrontFenderDamage { get; set; }
        public string RightFrontDoorDamage { get; set; }
        public string RightBackFenderDamage { get; set; }
        public string RightBackDoorDamage { get; set; }
        public string BonnetDamage { get; set; }
        public string RoofDamage { get; set; }
        public string BootDamage { get; set; }
        public string FrontBumperDamage { get; set; }
        public string BackBumperDamage { get; set; }
        public string FullServiceHistory { get; set; }
    }
    public class Product
    {
        public string ProductId { get; set; }
        public string ProductName { get; set; }
        public string ProductOptionId { get; set; }
        public string ProductOptionCode { get; set; }
        public string ProductOptionName { get; set; }
        public decimal ProductAmount { get; set; }
        public decimal ProductVATAmount { get; set; }
        public decimal ProductTotalAmount { get; set; }
        public decimal PayoverAmount { get; set; }
        public decimal PayoverVATAmount { get; set; }
        public decimal PayoverTotalAmount { get; set; }
        public string CommissionAmount { get; set; }
        public string CollectionFee { get; set; }
        public string PaymentType { get; set; }
        public string MonthlyPremium { get; set; }
        public string CoverAmount { get; set; }
        public string Term { get; set; }
        public string ExpiryDate { get; set; }
        public string mtnExpiryKilometres { get; set; }
        public string mtnManufacturerPlanType { get; set; }
        public string mtnManufacturerExpiryMonths { get; set; }
        public string mtnManufacturerExpiryKilometres { get; set; }
        public string warAdditionalMonths { get; set; }
        public string warManufacturerExpiryMonths { get; set; }
        public string SIFirstName { get; set; }
        public string SILastName { get; set; }
        public string SIIDType { get; set; }
        public string SIIDNumber { get; set; }
        public string SIGender { get; set; }
        public string SIMobileNumber { get; set; }
        public string SIWorkTelephoneCode { get; set; }
        public string SIWorkTelephoneNumber { get; set; }
        public string SIHomeTelephoneCode { get; set; }
        public string SIHomeTelephoneNumber { get; set; }
        public string SIEmailAddress { get; set; }
        public string SIAddressLine1 { get; set; }
        public string SIAddressLine2 { get; set; }
        public string SIAddressSuburb { get; set; }
        public string SIAddressCity { get; set; }
        public string SIAddressPostCode { get; set; }
        public string SIAddressProvinceName { get; set; }
        public string SIAddressCountryName { get; set; }
        public string FirstDebitDate { get; set; }
        public string FirstDebitAmount { get; set; }
        public string RecurringDebitDay { get; set; }
        public string RecurringDebitAmount { get; set; }
        public string CollectionAgent { get; set; }
        public decimal ProductPolicyFee { get; set; }
        public string ReferenceNo { get; set; }
        public decimal ProductInspectionFee { get; set; }
        public string mtnExistingPlanType { get; set; }
        public string mtnExistingExpiryDate { get; set; }
        public string mtnExistingExipryKilometrs { get; set; }
        public string warManufacturePlan { get; set; }
        public string warManufacturerExpiryKilometres { get; set; }
        public string warExistingPlan { get; set; }
        public string warExistingExpiryDate { get; set; }
        public string warExistingExpiryKilometres { get; set; }
        public string warAdditionalKilometres { get; set; }
        public string warExpiryKilometres { get; set; }
        public string SITitle { get; set; }
        public string SIRelationship { get; set; }
        public string ProductTypeCatergoryCode { get; set; }
        public string ProductTypeCatergoryName { get; set; }
        public string ProductOwner { get; set; }
        public string ProductAdministrator { get; set; }
        public string SIBirthDate { get; set; }
        public string ProductClaimsCompany { get; set; }
        public string ProductUnderwriterCompany { get; set; }
        public decimal ProductAdminFee { get; set; }
        public decimal BinderFee { get; set; }
        public decimal DealerDocumentationFee { get; set; }
        public decimal ValuationFee { get; set; }
        public string ProductNote { get; set; }
        public decimal SupplierRecovery { get; set; }
        public string BillToCompanyName { get; set; }
        public string OptionDisclosure { get; set; }
        public string OptionQuoteNumber { get; set; }
        private DateTime _Commencement { get; set; }
        public string Commencement
        {
            get { return _Commencement.ToString("dd-MMM-yyyy"); }
            set { _Commencement = DateTime.ParseExact(value, "dd-MMM-yyyy", System.Globalization.CultureInfo.InvariantCulture); }
        }
        public string mtnKilometresCommencement { get; set; }
        public string mtnAdditionalKilometres { get; set; }
        public string BeneficiaryFirstName { get; set; }
        public string BeneficiaryLastName { get; set; }
        public string BeneficiaryIDType { get; set; }
        public string BeneficiaryIDNumber { get; set; }
        public string BeneficiaryMobileNumber { get; set; }
        public string BeneficiaryWorkTelephoneCode { get; set; }
        public string BeneficiaryWorkTelephoneNumber { get; set; }
        public string BeneficiaryHomeTelephoneCode { get; set; }
        public string BeneficiaryHomeTelephoneNumber { get; set; }
        public string BeneficiaryRelationship { get; set; }
    }
    public class BankingDetails
    {
        public string BankName { get; set; }
        public string BankBranchName { get; set; }
        public string BankBranchCode { get; set; }
        public string BankAccountType { get; set; }
        public string BankAccountNumber { get; set; }
        public string AccountHolderName { get; set; }
    }
    public class Company
    {
        public string CompanyName { get; set; }
        public string CompanyRegistrationNumber { get; set; }
        public string CompanyVATNumber { get; set; }
        public string CompanyAddressLine1 { get; set; }
        public string CompanyAddressLine2 { get; set; }
        public string CompanyAddressSuburb { get; set; }
        public string CompanyAddressCity { get; set; }
        public string CompanyAddressPostCode { get; set; }
        public string CompanyAddressProvinceName { get; set; }
        public string CompanyAddressCountryName { get; set; }
    }
    public class cBatchNumber
    {
        public string BatchNumber { get; set; }
    }

}

Would it be a bad idea if I open the XML in code with Excel using Interop, then copy the contents of the excel sheet into a DataTable? Is something like that even possible?
Absolutely a bad idea. The Interop is very slow. It is better to use oledb connection. I do not think it will give you good results. The top layer tag is the Workbook name. Then next level tag is the worksheets. The next level is the column names and finally the rows data. Your XML file has Products and ProductDetails and it is not going to map properly to a workbook format. You need to manually parse data so each of my classes is a different worksheet. I have XmlArray and XmlArrayItem to take two levels of Xml tags and map to one class.

Collectives™ on Stack Overflow

How to read an XML File into a single DataTable C#

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related