Checking multiple XML files

Question

I am re-wording this from my original post: I have two XML files, and they are related to a given year each. For example, 18/19 and 17/18. They conform to the same structure and below is small sample from one of these files. What I want is, in C#, to compare all records in these files where the Given Name, the Family Name, the NI Number and the Date of birth are the same, BUT the Learner Ref Number is different. I need to be able to compare, then push only these records into a data table so I can then push them into a spreadsheet (the spreadsheet bit I can do). I currently have the below as a starting block, but am still very much stuck.

Firstly, I have my Import button press for which:

        private void Btn_Import_Click(object sender, RoutedEventArgs e)
    {


        ILRChecks.ILRReport.CrossYear();}

Then this goes to look at the Class of which eventually pushes the file to my location:

using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ILRValidation;
using InfExcelExtension;

namespace ILRChecks
{
internal static partial class ILRReport
{
    internal static void CrossYear()
    {
        DataSet ds_CrossYearChecks = 
ILRValidation.Validation.CrossYearChecks(Global.fileNames);

        string output = Path.Combine(Global.foldername, "ULIN_Issues" + 
".xlsx");


        ds_CrossYearChecks.ToWorkBook(output);




    }
}
}

And this is the bit I'm stuck on, which is the production of finding the differences:

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ILRValidation
{
public static partial class Validation
{
    public static DataSet CrossYearChecks(DataSet ds_CrossYearChecks)
    {
        return CrossYearChecks(ds_CrossYearChecks);
    }

    public static DataSet CrossYearChecks(string[] xmlPath)
    {

        DataSet ds_xmlCrossYear = new DataSet();


        return CrossYearChecks(ds_xmlCrossYear);
    }
}
}

XML:

<Learner>
<LearnRefNumber></LearnRefNumber>
<ULN></ULN>
<FamilyName></FamilyName>
<GivenNames></GivenNames>
<DateOfBirth></DateOfBirth>
<Ethnicity></Ethnicity>
<Sex></Sex>
<LLDDHealthProb></LLDDHealthProb>
<NINumber></NINumber>
<PriorAttain></PriorAttain>
<MathGrade></MathGrade>
<EngGrade></EngGrade>
<PostcodePrior></PostcodePrior>
<Postcode></Postcode>
<AddLine1></AddLine1>
<AddLine3></AddLine3>
<Email></Email>

Answer 1

Seems to me you're having trouble extracting the values you want from the xml, correct?

As the others have mentioned in the comments, without knowing the layout of your xml its impossible to give a specific example for your case. If you edit your question to include an example of your xml, we can help more.

Here are some general examples of how to extract values from xml:

private static bool CheckXmlDocument(string xmlPathCheck)
{
    // if you have multiple files from which you need to extract values, pass in an array or List<string> and loop over it, fetching the values

    // XmlDocument will allow you to edit the document as well as read it
    // there's another option to use XPathDocument and XPathNavigator but it's read-only
    var doc = new XmlDocument();

    // this can throw various exceptions so might want to add some handling
    doc.Load(xmlPathCheck);

    // getting the elements, you have some options depending on the layout of the document
    // if the nodes you want are identified by 'id' use this:
    var nameElement = doc.GetElementById("name");

    // if the nodes you want are identified by 'tag', use this:
    var nameNodeList = doc.GetElementsByTagName("name");

    // if you know the xpath to the specific node you want, use this:
    var selectNameNode = doc.SelectSingleNode("the/xpath/to/the/node");

    // if there are several nodes that have the same xpaths, use this:
    var selectNameList = doc.SelectNodes("the/xpath/that/may/match/many/nodes");

    // getting the value depends on the if you have an XmlNode, XmlElement or XmlNodeList
    // if you have a single XmlElement or XmlNode you can get the value one of these ways depending on the layout of your document:
    var name = nameElement.InnerText;
    name = nameElement.InnerXml;

    // if you have an XmlNodeList, you'll have to iterate through the nodes to find the one you want, like this:
    foreach (var node in nameNodeList)
    {
        // here use some condition that will determine if its the element/node you want or not (depends on your xml layout)
        if (node is XmlNode n)
        {
            name = n.InnerText;
        }
    }

    // do that for all the values you want to compare, then compare them
    return CheckValues(/*the values to compare*/);
}

XmlDocument

XmlNode

XmlElement

Answer 2

Well, you can traverse both XML files recursively and write down all the encountered changes. Something like should be helpful:

static string AppendPrefix(string oldPrefix, string addition) =>
    oldPrefix == "" ? addition : $"{oldPrefix}.{addition}";

static void CompareElements(string prefix, XElement d1, XElement d2)
{
    // 1. compare names
    var newPrefix = AppendPrefix(prefix, d1.Name.ToString());
    if (d1.Name != d2.Name)
    {
        Console.WriteLine(
            $"Name mismatch: {newPrefix} != {AppendPrefix(prefix, d2.Name.ToString())}");
        return;
    }

    // 2. compare attributes
    var attrs = d1.Attributes().OrderBy(a => a.Name);
    var unpairedAttributes = new HashSet<XAttribute>(d2.Attributes());
    foreach (var attr in attrs)
    {
        var otherAttr = d2.Attributes(attr.Name).SingleOrDefault();
        if (otherAttr == null)
        {
            Console.WriteLine($"No new attr: {newPrefix}/{attr.Name}");
            continue;
        }

        unpairedAttributes.Remove(otherAttr);
        if (attr.Value != otherAttr.Value)
            Console.WriteLine(
                $"Attr value mismatch: {newPrefix}/{attr.Name}: {attr.Value} != {otherAttr.Value}");
    }
    foreach (var attr in unpairedAttributes)
        Console.WriteLine($"No old attr: {newPrefix}/{attr.Name}");

    // 3. compare subelements
    var leftNodes = d1.Nodes().ToList();
    var rightNodes = d2.Nodes().ToList();
    var smallerCount = Math.Min(leftNodes.Count, rightNodes.Count);
    for (int i = 0; i < smallerCount; i++)
        CompareNodes(newPrefix, i, leftNodes[i], rightNodes[i]);
    if (leftNodes.Count > smallerCount)
        Console.WriteLine($"Extra {leftNodes.Count - smallerCount} nodes at old file");
    if (rightNodes.Count > smallerCount)
        Console.WriteLine($"Extra {rightNodes.Count - smallerCount} nodes at new file");
}

static void CompareNodes(string prefix, int index, XNode n1, XNode n2)
{
    if (n1.NodeType != n2.NodeType)
    {
        Console.WriteLine($"Node type mismatch: {prefix}/[{index}]");
        return;
    }

    switch (n1.NodeType)
    {
        case XmlNodeType.Element:
            CompareElements(prefix, (XElement)n1, (XElement)n2);
            break;
        case XmlNodeType.Text:
            CompareText(prefix, index, (XText)n1, (XText)n2);
            break;
    }
}

static void CompareText(string prefix, int index, XText t1, XText t2)
{
    if (t1.Value != t2.Value)
        Console.WriteLine($"Text mismatch at {prefix}[{index}]");
}

Usage:

XDocument d1 = <get document #1 from somewhere>,
          d2 = <get document #2 from somewhere>;

CompareNodes("", 0, d1.Root, d2.Root);

Obviously, instead of writing to console you should write to the appropriate spreadsheet.

Note that I'm ignoring the attribute reorder but not subnode reorder (which seems to be right).