Convret XML with all nodes to Excel in C#

Question

Hello Friends,

I am using below code to convert xml to excel But not able to bring complete xml into excel. There are only first level nodes date is coming in excel without child nodes. Can anybody help in this case.

 

 

This Is xml File: 

 

<Invoices>

<Invoice>

<Contact>

<ContactID>79475cda-87f1-459e-8709-fbf77cea6835</ContactID>

<Name>Bestpets</Name>

</Contact>

<Date>2020-10-01T00:00:00</Date>

<DueDate>2020-10-08T00:00:00</DueDate>

<PlannedPaymentDate>2020-10-02T00:00:00</PlannedPaymentDate>

<Status>DRAFT</Status>

<LineAmountTypes>Exclusive</LineAmountTypes>

<LineItems>

<LineItem>

<Description>601450101</Description>

<UnitAmount>99.82</UnitAmount>

<TaxType>INPUT2</TaxType>

<TaxAmount>19.98</TaxAmount>

<LineAmount>99.82</LineAmount>

<AccountCode>1690</AccountCode>

<Tracking>

<TrackingCategory>

<Name>Market Place</Name>

<Option>01 - None</Option>

<TrackingCategoryID>1896d5bf-5c81-4ec4-98f7-eaff2601a84b</TrackingCategoryID>

</TrackingCategory>

</Tracking>

<Quantity>1.0000</Quantity>

<LineItemID>50a2cda4-1b49-4ce1-89e1-ebff69431712</LineItemID>

</LineItem>

</LineItems>

<SubTotal>99.82</SubTotal>

<TotalTax>19.98</TotalTax>

<Total>119.80</Total>

<UpdatedDateUTC>2020-10-02T14:26:21.523</UpdatedDateUTC>

<CurrencyCode>GBP</CurrencyCode>

<FullyPaidOnDate>2020-10-02T00:00:00</FullyPaidOnDate>

<Type>ACCPAY</Type>

<InvoiceID>2ce181be-c04e-490a-a008-891ae5916b79</InvoiceID>

<InvoiceNumber>7191/301084</InvoiceNumber>

<Payments>

<Payment>

<PaymentID>0f35b2c0-c459-4b65-b9e2-70705d82c1a7</PaymentID>

<BatchPaymentID>5a8b4a30-850b-4681-a2bc-32c712d278a1</BatchPaymentID>

<Date>2020-10-02T00:00:00</Date>

<Amount>119.80</Amount>

<CurrencyRate>1.000000</CurrencyRate>

</Payment>

</Payments>

<AmountDue>0.00</AmountDue>

<AmountPaid>119.80</AmountPaid>

<AmountCredited>0.00</AmountCredited>

<CurrencyRate>1.000000</CurrencyRate>

<HasAttachments>true</HasAttachments>

<HasErrors>false</HasErrors>

</Invoice>

<Invoice>

<Contact>

<ContactID>79475cda-87f1-459e-8709-fbf77cea6835</ContactID>

<Name>Bestpets</Name>

</Contact>

<Date>2020-10-01T00:00:00</Date>

<DueDate>2020-10-08T00:00:00</DueDate>

<PlannedPaymentDate>2020-10-02T00:00:00</PlannedPaymentDate>

<Status>DRAFT</Status>

<LineAmountTypes>Exclusive</LineAmountTypes>

<LineItems>

<LineItem>

<Description>601536502</Description>

<UnitAmount>128.09</UnitAmount>

<TaxType>INPUT2</TaxType>

<TaxAmount>25.60</TaxAmount>

<LineAmount>128.09</LineAmount>

<AccountCode>1690</AccountCode>

<Tracking>

<TrackingCategory>

<Name>Market Place</Name>

<Option>01 - None</Option>

<TrackingCategoryID>1896d5bf-5c81-4ec4-98f7-eaff2601a84b</TrackingCategoryID>

</TrackingCategory>

</Tracking>

<Quantity>1.0000</Quantity>

<LineItemID>cd1a858a-359a-48b7-aa90-36278a7fd12b</LineItemID>

</LineItem>

</LineItems>

<SubTotal>128.09</SubTotal>

<TotalTax>25.60</TotalTax>

<Total>153.69</Total>

<UpdatedDateUTC>2020-10-02T14:26:22.997</UpdatedDateUTC>

<CurrencyCode>GBP</CurrencyCode>

<FullyPaidOnDate>2020-10-02T00:00:00</FullyPaidOnDate>

<Type>ACCPAY</Type>

<InvoiceID>b735d967-e3df-44b2-9635-223bed4ab860</InvoiceID>

<InvoiceNumber>7191/301083</InvoiceNumber>

<Payments>

<Payment>

<PaymentID>aaff83cb-b614-437a-b250-d2cf92685248</PaymentID>

<BatchPaymentID>5a8b4a30-850b-4681-a2bc-32c712d278a1</BatchPaymentID>

<Date>2020-10-02T00:00:00</Date>

<Amount>153.69</Amount>

<CurrencyRate>1.000000</CurrencyRate>

</Payment>

</Payments>

<AmountDue>0.00</AmountDue>

<AmountPaid>153.69</AmountPaid>

<AmountCredited>0.00</AmountCredited>

<CurrencyRate>1.000000</CurrencyRate>

<HasAttachments>true</HasAttachments>

<HasErrors>false</HasErrors>

</Invoice>

</Invoices>

 

 

 This is code: 

 

 

Excel.Application xlApp;

Excel.Workbook xlWorkBook;

Excel.Worksheet xlWorkSheet;

object misValue = Missing.Value;

DataSet ds = new DataSet();

XmlReader xmlFile;

xlApp = new Excel.ApplicationClass();

xlWorkBook = xlApp.Workbooks.Add(misValue);

xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

xmlFile = XmlReader.Create("MyXML.xml", new XmlReaderSettings());

ds.ReadXml(xmlFile, XmlReadMode.InferSchema);

for (int iRow = 1; iRow <= ds.Tables[0].Rows.Count - 1; iRow++)

{

for (int iCol = 0; iCol <= ds.Tables[0].Columns.Count - 1; iCol++)

{

xlWorkSheet.Cells[iRow + 1, iCol + 1] = ds.Tables[0].Rows[iRow].ItemArray[iCol].ToString();

}

}

Console.Write(xmlFile);

xlWorkBook.SaveAs(@"E:\Documents\Visual Studio 2015\WebSites\ConnectXero\Schema\xml2excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

xlWorkBook.Close(true, misValue, misValue);

xlApp.Quit();

releaseObject(xlApp);

releaseObject(xlWorkBook);

releaseObject(xlWorkSheet);