Querying multiple XML files with LINQ in C# Statements

Question

I'm attempting to extract data from a number of XML files posted on the Internet using LINQ. I'm working with LINQPad and using C# Statements. All files have the same format and Element names. My goal is to extract the same Elements from each file and then report the elements in one row per file, creating a grid of sorts. This would then ideally be exported to excel. I'm new to LINQ so any help would be greatly appreciated. Below is my working code:

// Load From Website.
        XElement Tags=XElement.Load("http://fapt.efanniemae.com/epooltalk-hvd/pool.xml?type=XML&pn="+"510299"+".XML");
        //XElement Tags=XElement.Load("http://fapt.efanniemae.com/epooltalk-hvd/pool.xml?type=XML&pn="+(list1)+".XML");
        XNamespace p = "http://fapt.efanniemae.com";

/Run Export

        var titles =
        from book in Tags.Descendants(p + "Pool")
        let bookAttributes = book.Element(p + "PoolFactors")
        let title = ((string)book.Element(p + "PoolNumber"))
        let title2 = ((string)bookAttributes.Element(p + "PoolFactor"))
        let month = (string)bookAttributes.Element (p + "Month")
        group title by month;

        foreach (var group in titles) {

        foreach (var title in group) {
        Console.WriteLine("Pool Num |" + title);
}
}
        foreach(XElement CusipElement in Tags.Descendants(p + "CUSIP")) {
        Console.WriteLine("CUSIP |" +(string)CusipElement);
        }
        foreach(XElement PrefixElement in Tags.Descendants(p + "PoolPrefix")) {
        Console.WriteLine("PoolPrefix |" +(string)PrefixElement);
        }
        foreach(XElement ObalElement in Tags.Descendants(p + "OriginalSecurityBalance")) {
        Console.WriteLine("Orig. Bal |" +(string)ObalElement);
        }
        foreach(XElement OtermElement in Tags.Descendants(p + "WeightedAverageOrigLoanTerm")) {
        Console.WriteLine("Orig. Term |" +(string)OtermElement);
        }
        foreach(XElement RtermElement in Tags.Descendants(p + "WAMnthsRemainingToAmortization")) {
        Console.WriteLine("Remain Term |" +(string)RtermElement);
        }
        foreach(XElement WalaElement in Tags.Descendants(p + "WeightedAverageLoanAge")) {
        Console.WriteLine("WALA |" +(string)WalaElement);
        }
        foreach(XElement AccrateElement in Tags.Descendants(p + "CurrentAccrualRate")) {
        Console.WriteLine("Net Rate |" +(string)AccrateElement);
        }           
        foreach(XElement MarginElement in Tags.Descendants(p + "WeightedAverageLoanMarginRate")) {
        Console.WriteLine("WA Margin |" +(string)MarginElement);
        }
        foreach(XElement SubtElement in Tags.Descendants(p + "SubType")) {
        Console.WriteLine("SubType |" +(string)SubtElement);
        }
        //foreach(XElement MonthElement in Tags.Descendants(p + "Month"))
        //foreach(XElement WacElement in Tags.Descendants(p + "WAC")) {
        //Console.WriteLine("WAC |" +(string)WacElement + "|" +(string)MonthElement);
        //}
        foreach(XElement UpdatedcapElement in Tags.Descendants(p + "UpdatedCap")) {
        Console.WriteLine("Updated CAP |" +(string)UpdatedcapElement);
        }
        foreach(XElement IdateElement in Tags.Descendants(p + "IssueDate")) {
        Console.WriteLine("Issue Date |" +(string)IdateElement);
        }
        foreach(XElement MdateElement in Tags.Descendants(p + "MaturityDate")) {
        Console.WriteLine("Maturity Date |" +(string)MdateElement);
        }
        foreach(XElement RadjElement in Tags.Descendants(p + "RateAdjustmentFrequency")) {
        Console.WriteLine("Rate Adj Freq |" +(string)RadjElement);
        }
        foreach(XElement PcapElement in Tags.Descendants(p + "PerAdjustmentCap")) {
        Console.WriteLine("Period Cap |" +(string)PcapElement);
        }
        foreach(XElement PchgfreqElement in Tags.Descendants(p + "PaymentChangeFrequency")) {
        Console.WriteLine("Pymt Chg Freq |" +(string)PchgfreqElement);
        }
        foreach(XElement MtrElement in Tags.Descendants(p + "WeightedAverageMonthsToRoll")) {
        Console.WriteLine("WA MTR |" +(string)MtrElement);
        }
        foreach(XElement RatecapElement in Tags.Descendants(p + "WeightedAverageCap")) {
        Console.WriteLine("WA CAP |" +(string)RatecapElement);
        }
var Months = Tags.Descendants(p + "Month")
        .Select(titleElement => (string)titleElement);
foreach (string title in Months) {
Console.WriteLine("Months |" + title);
}
var Wacs = Tags.Descendants(p + "WAC")
            .Select(titleElement => (string)titleElement);
foreach (string title in Wacs) {
Console.WriteLine("WAC |" + title);
}
var Wams = Tags.Descendants(p + "WAM")
            .Select(titleElement => (string)titleElement);
foreach (string title in Wams) {
Console.WriteLine("WAM |" + title);
}
var Factors = Tags.Descendants(p + "Factor")
            .Select(titleElement => (string)titleElement);
foreach (string title in Factors) {
Console.WriteLine("Factor |" + title);
}

How do I get the queried elements to appear horizontal and with some delimiter?

Currently my code only works for 1 XML file. How can this be altered to loop for multiple files? The source file names all have the same base URL with the only difference being the ending statement. Is there a way to make the Load reference the base URL concatenated with a variable list which would contain the ending statement?

Open to any and all suggestions.

Answer 1

I saw this question while looking at another question on Stack Overflow, and noticed that this one is still unanswered. I don't know if this is still an issue for you, but for anyone else reading this, this is one way to perform an LINQ query towards several sources.

The trick is to use the [Enumerable.Concat(TSource) Method][1].

XElement tags=XElement.Load("http://fapt.efanniemae.com/epooltalk-hvd/pool.xml?type=XML&pn="+"510299"+".XML");
XElement tags2=XElement.Load("http://fapt.efanniemae.com/epooltalk-hvd/pool.xml?type=XML&pn="+(list1)+".XML");

var titles =
    from book in tags.Descendants(p + "Pool").Concat(tags2.Descendants(p + "Pool"))
    let bookAttributes = book.Element(p + "PoolFactors")
    let title = ((string)book.Element(p + "PoolNumber"))
    let title2 = ((string)bookAttributes.Element(p + "PoolFactor"))
    let month = (string)bookAttributes.Element (p + "Month")
    group title by month;

I hope this helps you or anyone.

[1]: http://msdn.microsoft.com/en-us/library/bb302894.aspx Enumerable.Concat(TSource) Method

Answer 2

It looks like you put some decent effort into this, so hats off to you. I recognize some beginner LINQ mistakes or misunderstandings from your code and hope to address them below.

1. When you expect one element to exist, do not use Descendants. Use Element instead. Everyting from "Pool Num" to "WA CAP" is a stand-alone XML element that can be retrieved directly in this manner: parent.Element(p + "PoolNumber") where parent is the desired element's parent element.
2. To get the element's value use the Value property: parent.Element(p + "PoolNumber").Value. Using the (string) cast is not incorrect, however it's preferable to use it when you suspect that the element may or may not exist. If it doesn't exist calling Value would return a NullReferenceException since it would be null. Casting it gets around this. Easy way to test this in my code below is to add a pool.Element(p + "PoolNumber").Remove(); after the declaration of pool and watch it break. Then use your (string) approach and watch it happily continue.
3. Related to point #1, the Element approach effectively replaces the need to foreach over the result just to get one value. I recommend playing around with First, Single, FirstOrDefault and SingleOrDefault methods. You have LINQPad so check out the examples and play with them.

Apart from that, your local variable names should begin with a lowercase per the standard formatting expectations. It's also helpful to line up your LINQ method calls on separate lines and align them at the start of the dot notation.

How do I get the queried elements to appear horizontal and with some delimiter?

Use the String.Join method. With .NET 4.0 there's no need to call ToArray since the method accepts an overload for IEnumerable<string>.

Currently my code only works for 1 XML file. How can this be altered to loop for multiple files?

Place your pool number values in a list then foreach over it and place the logic in the body of the loop. See my code below.

Here's the cleaned up version of your code. I wasn't sure whether you wanted all the headers to be horizontal or were only concerned with using a delimiter on items with multiple values.

// load from websites based on pool numbers in list
var list = new List<string> { "510299", "510300"};
foreach (var poolNumber in list)
{
    XElement tags=XElement.Load("http://fapt.efanniemae.com/epooltalk-hvd/pool.xml?type=XML&pn=" + poolNumber + ".XML");
    XNamespace p = tags.GetDefaultNamespace();

    // export process

    XElement pool = tags.Element(p + "Pool");
    Console.WriteLine("Pool Num |" + pool.Element(p + "PoolNumber").Value);
    Console.WriteLine("CUSIP |" + pool.Element(p + "CUSIP").Value);
    Console.WriteLine("PoolPrefix |" + pool.Element(p + "PoolPrefix").Value);
    Console.WriteLine("Orig. Bal |" + pool.Element(p + "OriginalSecurityBalance").Value);
    Console.WriteLine("Orig. Term |" + pool.Element(p + "WeightedAverageOrigLoanTerm").Value);
    Console.WriteLine("Remain Term |" + pool.Element(p + "WAMnthsRemainingToAmortization").Value);
    Console.WriteLine("WALA |" + pool.Element(p + "WeightedAverageLoanAge").Value);
    Console.WriteLine("Net Rate |" + pool.Element(p + "CurrentAccrualRate").Value);
    Console.WriteLine("WA Margin |" + pool.Element(p + "WeightedAverageLoanMarginRate").Value);
    Console.WriteLine("SubType |" + pool.Element(p + "SubType").Value);
    Console.WriteLine("Updated CAP |" + pool.Element(p + "UpdatedCap").Value);
    Console.WriteLine("Issue Date |" + pool.Element(p + "IssueDate").Value);
    Console.WriteLine("Maturity Date |" + pool.Element(p + "MaturityDate").Value);
    Console.WriteLine("Rate Adj Freq |" + pool.Element(p + "RateAdjustmentFrequency").Value);
    Console.WriteLine("Period Cap |" + pool.Element(p + "PerAdjustmentCap").Value);
    Console.WriteLine("Pymt Chg Freq |" + pool.Element(p + "PaymentChangeFrequency").Value);
    Console.WriteLine("WA MTR |" + pool.Element(p + "WeightedAverageMonthsToRoll").Value);
    Console.WriteLine("WA CAP |" + pool.Element(p + "WeightedAverageCap").Value);

    var poolFactors = pool.Element(p + "PoolFactors");
    var months = poolFactors.Descendants(p + "Month")
                            .Select(m => m.Value);
    Console.WriteLine("Months |" + String.Join(", ", months.ToArray()));

    var wacs = poolFactors.Descendants(p + "WAC")
                          .Select(wac => wac.Value);
    Console.WriteLine("WAC |" + String.Join(", ", wacs.ToArray()));

    var wams = poolFactors.Descendants(p + "WAM")
                          .Select(wam => wam.Value);
    Console.WriteLine("WAM |" + String.Join(", ", wams.ToArray()));

    var factors = poolFactors.Descendants(p + "Factor")
                             .Select(f => f.Value);
    Console.WriteLine("Factor |" + String.Join(", ", factors.ToArray()));

    Console.WriteLine();
}