Get the value of an element, but only when the element above it has a specific value

Question

First time posting on Stack Overflow.

I am struggling with handling a specific XML structure in C#

The below XML represents columns and their respective values in a table. Naturally you can have multiple rows.

 <?xml version="1.0" encoding="UTF-16"?>
<DataTable Uid="BOY_2">
    <Columns>
        <Column Uid="Col1" Type="1" MaxLength="1"/>
        <Column Uid="DocEntry" Type="2" MaxLength="0"/>
        <Column Uid="DocNum" Type="2" MaxLength="0"/>
        <Column Uid="Doctotal" Type="11" MaxLength="0"/>
    </Columns>
    <Rows>
        <Row>
            <Cells>
                <Cell>
                    <ColumnUid>Col1</ColumnUid>
                    <Value>Y</Value>
                </Cell>
                <Cell>
                    <ColumnUid>DocEntry</ColumnUid>
                    <Value>10</Value>
                </Cell>
                <Cell>
                    <ColumnUid>DocNum</ColumnUid>
                    <Value>365</Value>
                </Cell>
                <Cell>
                    <ColumnUid>Doctotal</ColumnUid>
                    <Value>175.730000</Value>
                </Cell>
            </Cells>
        </Row>
        <Row>
            <Cells>
                <Cell>
                    <ColumnUid>Col1</ColumnUid>
                    <Value>Y</Value>
                </Cell>
                <Cell>
                    <ColumnUid>DocEntry</ColumnUid>
                    <Value>12</Value>
                </Cell>
                <Cell>
                    <ColumnUid>DocNum</ColumnUid>
                    <Value>366</Value>
                </Cell>
                <Cell>
                    <ColumnUid>Doctotal</ColumnUid>
                    <Value>173.970000</Value>
                </Cell>
            </Cells>
        </Row>
    </Rows>
</DataTable>

I am trying to get it so that if the element under cell called ColumnUid = Col1, to add the value in the element below called value to a list.

So in my example I just want to get the value 'Y' from both rows in the XML.

I can't for the life of me figure out how to get this to work. Please help!

Please excuse my poor wording of this, I am new to programming and dealing with XML.

Answer 1

You can read into a datatable using xml linq with following code :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using System.Data;
using System.IO;
namespace ConsoleApplication180
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.xml";
        static void Main(string[] args)
        {
            StreamReader reader = new StreamReader(FILENAME);
            reader.ReadLine(); //skip UTF-16
            XDocument doc = XDocument.Load(reader);

            DataTable dt = new DataTable();

            string[] columnsNames = doc.Descendants("Column").Select(x => (string)x.Attribute("Uid")).ToArray();
            DataColumn[] columns = columnsNames.Select(x => new DataColumn(x)).ToArray();
            dt.Columns.AddRange(columns);

            foreach (XElement cells in doc.Descendants("Cells"))
            {
                DataRow row = dt.Rows.Add();

                foreach (XElement cell in cells.Elements("Cell"))
                {
                    row[(string)cell.Element("ColumnUid")] = (string)cell.Element("Value");
                }

            }

        }
    }
}