Read hierarchical XML and flatten into a List of objects?

Question

I have an XML document which I would like to read, flatten, and return a List<> of an object I called PMRow. For each CodingRow in the XML there should be a corresponding List element and the "header" details of the Invoice should simply repeat. Many of the XML elements will be ignored in this transformation at both the Invoice and CodingRow level. I've started writing the code below and I can't conceptualize where to go from there:

Incomplete code:

public static List<PMRow> ParseToPMRows(FileInfo myFile)
{
    var xDoc = XDocument.Load(myFile.FullName);

    var query = from element in xDoc.Element("InvoiceDocument").Element("Invoice").Element("CodingRows").Elements("CodingRow")
                select 
}

Target Class:

public class PMRow
{
    public string SupplierCode { get; set; }
    public string InvoiceNumber { get; set; }
    public DateTime InvoiceDate { get; set; }
    public decimal GrossSum { get; set; }
    public decimal NetSum { get; set; }
    public decimal TaxSum { get; set; }
    public decimal CR_GrossSum { get; set; }
    public string AccountNumber { get; set; }
    public string DimCode1 { get; set; }
}

XML Docuemnt:

<InvoiceDocument>
<Method>Post</Method>
<Invoice>
    <GrossSum nil="False">700.000000</GrossSum>
    <InvoiceDate nil="False">2018-09-26</InvoiceDate>
    <InvoiceNumber nil="False">180928003802901</InvoiceNumber>
    <NetSum nil="False">700.000000</NetSum>
    <PaidSum nil="False">0.000000</PaidSum>
    <PaymentBlock nil="False">false</PaymentBlock>
    <PaymentDate nil="False">0001-01-01</PaymentDate>
    <SupplierCode nil="False">AQUINC</SupplierCode>
    <SupplierParentId nil="False"></SupplierParentId>
    <TaxCode nil="False"></TaxCode>
    <TaxPercent nil="False">0.000000</TaxPercent>
    <TaxPercent2 nil="False">0.000000</TaxPercent2>
    <TaxSum nil="False">0.000000</TaxSum>
    <OrderNumber nil="False"></OrderNumber>
    <OrderInCoding nil="False" />
    <CodingRows>
        <CodingRow>
            <GrossSum nil="False">500.000000</GrossSum>
            <InternalStatus nil="False">Loaded</InternalStatus>
            <AccountCode nil="False">1990</AccountCode>
            <AccountName nil="False">Gain on Non-Operating Asset</AccountName>
            <DimCode1 nil="False">01</DimCode1>
            <DimName1 nil="False">Operating/Unrestricted</DimName1>
            <MaterialGroup nil="False"></MaterialGroup>
            <FiscalYear nil="False"></FiscalYear>
            <DimCode3 nil="False">06</DimCode3>
            <DimName3 nil="False">Sports</DimName3>
            <DimCode4 nil="False">06500</DimCode4>
            <DimName4 nil="False">Personal Training</DimName4>
            <DimCode5 nil="False">6</DimCode5>
            <DimName5 nil="False">Minneapolis</DimName5>
            <DimCode6 nil="False"></DimCode6>
            <DimName6 nil="False"></DimName6>
        </CodingRow>
        <CodingRow>
            <GrossSum nil="False">200.000000</GrossSum>
            <InternalStatus nil="False">Loaded</InternalStatus>
            <AccountCode nil="False">2390</AccountCode>
            <AccountName nil="False">Gain on Non-Operating Asset</AccountName>
            <DimCode1 nil="False">02</DimCode1>
            <DimName1 nil="False">Operating/Unrestricted</DimName1>
            <MaterialGroup nil="False"></MaterialGroup>
            <FiscalYear nil="False"></FiscalYear>
            <DimCode3 nil="False">06</DimCode3>
            <DimName3 nil="False">Sports</DimName3>
            <DimCode4 nil="False">06500</DimCode4>
            <DimName4 nil="False">Personal Training</DimName4>
            <DimCode5 nil="False">6</DimCode5>
            <DimName5 nil="False">Minneapolis</DimName5>
            <DimCode6 nil="False"></DimCode6>
            <DimName6 nil="False"></DimName6>
        </CodingRow>
    </CodingRows>
    <InvoiceRows />
</Invoice>

Conceptualized target (two objects of type PMRow in a List):

AQUINC, 180928003802901, 9/26/2018, 700, 700, 0, 500, 1990, 01

AQUINC, 180928003802901, 9/26/2018, 700, 700, 0, 200, 2390, 02

Answer 1

Try code below. I assumed they are multiple Invoice in a file so I had to use SelectMany() to return a flat list

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

namespace ConsoleApplication75
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.xml";
        static void Main(string[] args)
        {
            ParseToPMRows(FILENAME);
        }
        public static List<PMRow> ParseToPMRows(string myFile)
        {
            XDocument xDoc = XDocument.Load(myFile);


            var tempResults = xDoc.Descendants("Invoice").Select(x => new {
                supplierCode = (string)x.Element("SupplierCode"),
                invoiceNumber = (string)x.Element("InvoiceNumber"),
                invoiceDate = (DateTime)x.Element("InvoiceDate"),
                grossSum = (decimal)x.Element("GrossSum"),
                netSum = (decimal)x.Element("NetSum"),
                taxSum = (decimal)x.Element("TaxSum"),
                codingRows = x.Descendants("CodingRow").Select(y => new {
                    crGrossSum = (decimal)y.Element("GrossSum"),
                    accounNumber = (string)y.Element("AccountCode"),
                    dimCode1 = (string)y.Element("DimCode1")
                }).ToList()
            }).ToList();

            List<PMRow> rows = tempResults.Select(x => x.codingRows.Select(y => new PMRow()
            {
                SupplierCode = x.supplierCode,
                InvoiceNumber = x.invoiceNumber,
                InvoiceDate = x.invoiceDate,
                GrossSum = x.grossSum,
                NetSum = x.netSum,
                TaxSum = x.taxSum,
                CR_GrossSum = y.crGrossSum,
                AccountNumber = y.accounNumber,
                DimCode1 = y.dimCode1
            })).SelectMany(x => x).ToList();

            return rows;
         }
    }
    public class PMRow
    {
        public string SupplierCode { get; set; }
        public string InvoiceNumber { get; set; }
        public DateTime InvoiceDate { get; set; }
        public decimal GrossSum { get; set; }
        public decimal NetSum { get; set; }
        public decimal TaxSum { get; set; }
        public decimal CR_GrossSum { get; set; }
        public string AccountNumber { get; set; }
        public string DimCode1 { get; set; }
    }
}

For one Invoice you can use following :

        public static List<PMRow> ParseToPMRows(string myFile)
        {
            XDocument xDoc = XDocument.Load(myFile);


            var tempResults = xDoc.Descendants("Invoice").Select(x => new {
                supplierCode = (string)x.Element("SupplierCode"),
                invoiceNumber = (string)x.Element("InvoiceNumber"),
                invoiceDate = (DateTime)x.Element("InvoiceDate"),
                grossSum = (decimal)x.Element("GrossSum"),
                netSum = (decimal)x.Element("NetSum"),
                taxSum = (decimal)x.Element("TaxSum"),
                codingRows = x.Descendants("CodingRow").Select(y => new {
                    crGrossSum = (decimal)y.Element("GrossSum"),
                    accounNumber = (string)y.Element("AccountCode"),
                    dimCode1 = (string)y.Element("DimCode1")
                }).ToList()
            }).FirstOrDefault();

            List<PMRow> rows = tempResults.codingRows.Select(x => new PMRow()
            {
                SupplierCode = tempResults.supplierCode,
                InvoiceNumber = tempResults.invoiceNumber,
                InvoiceDate = tempResults.invoiceDate,
                GrossSum = tempResults.grossSum,
                NetSum = tempResults.netSum,
                TaxSum = tempResults.taxSum,
                CR_GrossSum = x.crGrossSum,
                AccountNumber = x.accounNumber,
                DimCode1 = x.dimCode1
            }).ToList();

            return rows;
         }

Answer 2

I generally find more convenient to first serialize XML into class object and then iterate over serialzed object.

You only need

XmlSerializer serializer = new XmlSerializer(typeof(Invoice));

TextReader reader = new StringReader(source);

Invoice resultObj = (Invoice)serializer.Deserialize(reader);

var pmRows = resultObj.CodingRows.CodingRow.Select(item => new PMRow
{
     CR_GrossSum = Convert.ToDecimal(resultObj.GrossSum.Text),
     InvoiceDate = Convert.ToDateTime(resultObj.InvoiceDate.Text),
     InvoiceNumber = resultObj.InvoiceNumber.Text,
     SupplierCode = resultObj.SupplierCode.Text,
     NetSum = Convert.ToDecimal(resultObj.NetSum.Text),
     GrossSum = Convert.ToDecimal(resultObj.GrossSum.Text),
     TaxSum = Convert.ToDecimal(resultObj.TaxSum.Text),
     AccountNumber = item.AccountCode.Text,
     DimCode1 = item.DimCode1.Text
 }).ToList();

you can check my solution from https://dotnetfiddle.net/jr11hB

Answer 3

Is this what you are looking for?

    XElement invoice = xDoc.Root.Element("Invoice");

    List<PMRow> rows = invoice
        .Element("CodingRows")
        .Elements("CodingRow")
        .Select(codingRow => new PMRow
        {
            SupplierCode = invoice.Element("SupplierCode").Value,
            InvoiceNumber = invoice.Element("InvoiceNumber").Value,
            InvoiceDate = DateTime.Parse(invoice.Element("InvoiceDate").Value),
            GrossSum = decimal.Parse(invoice.Element("GrossSum").Value),
            NetSum = decimal.Parse(invoice.Element("NetSum").Value),
            TaxSum = decimal.Parse(invoice.Element("TaxSum").Value),
            CR_GrossSum = decimal.Parse(codingRow.Element("GrossSum").Value),
            AccountNumber = codingRow.Element("AccountCode").Value,
            DimCode1 = codingRow.Element("DimCode1").Value,
        })
        .ToList();

Note: the above assumes that all elements will be present and valid. If this is not the case, you will need to add appropriate handling for that.

Fiddle: https://dotnetfiddle.net/DjKcDg