Click here to Skip to main content
15,877,103 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a large set of XML data (450,000 rows) returned by an API that I am attempting to roll up into a summary. I thought of using LiNQ with an embedded xPath call to return the counts. Maybe my approach is incorrect??? Any suggestions would be helpful!

What I have tried:

XmlElement ParserMap = (XmlElement)Parameters.SelectSingleNode("ParserMap");
XElement adoc = XElement.Parse(xdoc.InnerXml);
string Criteria = ParserMap.Attributes["CodeList"].InnerText;

//Used this to get by the root element.
XmlElement wDoc = (XmlElement)xdoc.LastChild;

//Preferred method, would allow reshaping of data in one statement.  Getting error on xPath matching.... Invalid token, doesn't return result????
var aCounts = from aC in adoc.Descendants("record")
              group aC by new { ID = (string)aC.Element("id"), 
                      codeid = (string)aC.Element("codeid"),
                      adate = (string)aC.Element("adate"), 
                      pcode = (string)aC.Element("pcode"),
                      Afield = wDoc.SelectNodes($"descendant::
                         record[id = '{(string)aC.Element("id")}' and 
                         codeid = ('{Criteria.Replace(",", "','")}')]").Count}
                    into g select new { g.Key, g } ;

//Clean code and return 1,000 records but would require a loop to put the counts on the first occurrence of the .Where filter. 
var alCounts = adoc.Descendants("record")
                   .Where(aL => Criteria.Contains((string)aL.Element("codeid")))
                   .GroupBy(r => r.Element("id"));

Criteria = ParserMap.Attributes["AnotherCodeList"].InnerText;
//Clean code and returns 0 records as it should...  When a .Where is true would require a variable length foreach to place the count on the first occurrence of the match.
var tCounts = adoc.Descendants("record")
                  .Where(aL => Criteria.Contains((string)aL.Element("codeid")))
                  .GroupBy(r => r.Element("id"));

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900