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.