query xml data to text output

JEsradi
JEsradi used Ask the Experts™
on
Background:
I'm pretty good with VBA, VB6, and competent with VB.NET and javascript.  But the new boss is a C# guy.  All new projects going forward will be C#, so I've been learning, teaching myself, betwixt ongoing VB projects.  I'm past the 'Hello, world' stage, but not by much!
Yesterday he comes to me with a request to convert a VBA function I wrote to format data from an Access database, into a C# class that formats that data, now in an xml file, the same way.  So I know everything that needs to be done, but have absolutely no idea how to go about it!
I've got big points for anybody who's willing to hold my hand and walk me thru this!

Scenario (see attached 80018210.xml):
The xml data describes a daily report from a rail-flaw detection vehicle, using induction and ultrasonics to test railroad tracks for defects (cracks, wear, internal flaws).
The Reports collection contains the service data for the day.  There is one Report node for each customer serviced in a day.
Within a Report node, there is one Location node per customer location or within a location with a non-contiguous milepost.
Within a Location node, there is a Movement node for every action performed on the track(or road) in that location, as well as a Defect node, if any defects were marked.
In the Movement node, an MCODE of 0 represents the beginning of a test, an MCODE of 7 represents the movement half of a defect record, and an MCODE of 5 marks the end of a test.
The example report describes the 'worst case' scenario for the purposes of the output I need:  there are 4 Location nodes (LOCID), but only 2 distinct customer locations (LNNUM), 7100 and 7101, with the added twist that 7101 exists between different segments of 7100.

Output format:
Each customer location, by LNNUM, is output to a separate file.
Each file begins with a Header line, containing:  LNNUM, Total miles tested, number of tested segments, CAR number, number of Defects found, Date, Operator, Total test time, total Delay time, etc.
The next lines describe the tested segments: the TrackID, and the mileposts at the beginning and end of the test.
If any defects were found within the milepost range of a tested segment, that defect must be listed under the segment in which it was found.  Defect info includes milepost, trackside (L/R), defect type, defect size, defect number, etc.

Requirements:
an array of unique LNNUMs in the Report
a way to combine the Movement nodes of multiple Location nodes into a single dataset or hashtable, so total time and miles for the specific LNNUM can be calculated.
a technique to capture the mileposts of the 0-5 pairs that comprise a test segment.
a way to insert the defects directly below the correct test segment in the set of test segments.

Plea:
If anyone would like to take a shot at this with me, please let me know soonest...my bossman knows I'm on a learning curve - he's given me to Tuesday next to come up with something.  Thank goodness for the 4th of July, or he'd want it Monday!

In the meantime, it's back to search-and-skim, looking for answers to questions I'm not sure how to ask!

Ginny
80018210.xml
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
anarki_jimbelSenior Developer

Commented:
From what you say, the task is not a C# problem really but probably xml handling task.
Anyway, if you are competent in VB.Net - you may try to write your code in VB.Net and convert to C# using one of online tools...
Fernando SotoRetired
Distinguished Expert 2017

Commented:
What version of Visual Studio are you using, 2005, 2008 or 2010?
If you are using VS 2008 or 2010 can you use the new technology Linq, Language Independent Query, in place of using XPath and XML technology?

Author

Commented:
xml handling task, aye...and there's the rub - this is my FIRST xml handling task.  I am competent at VB.NET as far as those things I've needed to learn and understand, I have learned and understood.  I can spend my time learning how to handle xml queries in VB.NET, or I can spend time learning to do it with C#.  VB.NET is much closer to my comfort zone, but my EMPLOYER prefers C#.  Quandary.  Either way, I don't have a lot of time and that's why I'm here practically begging for help, instead of searching the knowledge base for almost-answers and figuring it out from there, like I normally do.  Well, that IS what I'm doing, but I'm hoping some kind soul will come up with an A,B,C, foreach-node-like-this to build an arraylist; 1,2,3,selectNode-like-so to navigate the Movement records; so I can concentrate on the desired result instead of researching the syntax of an almost-solution, until I understand it enough to change it to meet my needs.

But thank you for your response and know that that IS the path I am on, and will continue on most likely, as who has time for this shit with a holiday weekend on the way?
Thanks again.

Ginny
Fernando SotoRetired
Distinguished Expert 2017

Commented:
OK but what version of Visual Studio are you using?

Author

Commented:
FernandoSoto,

VS 2008.  Ah, LINQ, a new word to add to my vocabulary...amazing what you can find out if you know what words to use, eh?  I'm reading up on Linq now, thanks.  I don't know of any reason why I can't use it...yet.

Thanks for the tip.

Ginny
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Linq which is pronounced as if it were spelled as "link" can be used to query the XML document with very little code. I do not know XPath and the XmlDocument classes very well to be able to help taking that path but if you want to use Linq I can help.
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Hi Ginny;

The code snippet below is what is needed to get, "an array of unique LNNUMs in the Report"

Fernando
using System.Xml.Linq;

// Load the XML document 
XDocument xdoc = XDocument.Load("80018210.xml");
// Get reference to root element
XElement root = xdoc.Root;

// Get all the unique LNNUMs in the documents
List<String> uniqueLNNUMs = (from LNNUM in root.Descendants("LNNUM")
                             select LNNUM.Value).Distinct().ToList();

// Display all the unique LNNUMs
foreach (var lnnum in uniqueLNNUMs)
{
    Console.WriteLine(lnnum);
}

Open in new window

Author

Commented:
Fernando,

I suppose the good thing about my raw noob status with xml is that I have no pre-conceived notions about the relative merits of XPath and the DOM vs Linq...but I like the sound of 'very little code'!  Your help would be greatly appreciated.

Ginny
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Hi Ginny;

Can you answer my questions to your Requirements and what should this all look like after the queries are completed? Also any other information that will help.

Requirements:
an array of unique LNNUMs in the Report

I understand This
=============================================
a way to combine the Movement nodes of multiple Location nodes into a single dataset or hashtable, so total time and miles for the specific LNNUM can be calculated.

What fields of the Movement nodes need to be in the DataTable or HashTable. How does it need to be grouped? How are calculations made?
=============================================
a technique to capture the mileposts of the 0-5 pairs that comprise a test segment.

Not sure what you mean by this, can you give more info?
=============================================
a way to insert the defects directly below the correct test segment in the set of test segments.

How do you what want this formatted?
=============================================


Fernando

Author

Commented:
Fernando,

Ok, so I kept adding things until I got rid of all the compile errors: using, namespace, class, Main, return, etc.  From the immediate window I run: ?createUniqueLNNUMlist("D:\Program Files\DCS.NET\Data\80018210.xml").
a console window opens, but nothing is displayed, and the return at the immediate window is "Unrecognized escape sequence".

I'm obviously missing some basic background knowledge...

Ok, just saw your questions.  Took a minute to import the xml report into the relational database it mirrors, and then was able to run the original VBA function against it.  I've pasted the results below, 1 set of records per customer LNNUM.

'450,7100,35.5,3,SRS800,1,20100701, RB,0050,0042,0467,0017,0000,0042,0000,0617,0,0,0,"test report, multiple lines, at least one duplic"'
'460,SG,,91.910,,91.970,,,,,'
'460,1,,91.970,,109.670,,,,,'
'460,2,,109.670,,91.970,,,,,'
'400,101.24,,L,131,RD,W,0,SC,BS,1986,2,15,+44.234567,-77.121212," "'
'000

'450,7101,4.5,1,SRS800,1,20100701, RB,0000,0000,0050,0000,0033,0000,0000,0083,0,0,0,"test report, multiple lines, at least one duplic"'
'460,SG,,0.000,,4.500,,,,,'
'400,3.25,,T,132,LV,W,0,AH,BL,1988,1,1.5,+44.233156,-77.596123," "'
'000

Argh, interruption (I also do phone support for the operators of the test cars).  More to follow.

Ginny

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
        }
        public List<string> createUniqueLNNUMlist(string filename)
        {
            // Load the XML document 
            XDocument xdoc = XDocument.Load(filename);
            // Get reference to root element
            XElement root = xdoc.Root;

            // Get all the unique LNNUMs in the documents
            List<string> uniqueLNNUMs = (from LNNUM in root.Descendants("LNNUM")
                                         select LNNUM.Value).Distinct().ToList();

            // Display all the unique LNNUMs
            foreach (var lnnum in uniqueLNNUMs)
            {
                Console.WriteLine(lnnum);
            }
            return uniqueLNNUMs;
        }
    }
}

Open in new window

Author

Commented:
Ok, where was I?

The '450 prefix indicates the header/summary record, with the 4-digit number strings representing times (hhmm)
Times and miles are grouped by LNNUM and MCODE.  If nodes were tables, for test time and miles:

SELECT LNNUM, MCODE, Sum([CHRGTIME]) AS TTIME, Sum([EMILES]) AS TMILES
FROM Location INNER JOIN Movement ON Location.LOCID = Movement.LOCID
GROUP BY LNNUM, MCODE
HAVING LNNUM="7100" AND MCODE=5;

rinse and repeat for MCODE=3 (delay time) and MCODE=4 (run time).
The queries are actually a little more complex than that, as CHRGCODE is also a consideration (company time, customer time, down for repairs), and this particular customer distinguishes between track and road travel time (TRACKID), and Traffic Delay vs Non-Traffic Delay Time (NTDT), but you get the picture.  I need all the movement nodes in one collection to perform these kind of queries...don't I?

The '460 prefix indicates a tested segment, being a section of contiguous track between 2 milepost markers.  MCODE=0 is a start test record and captures the TrackID and begin milepost.  MCODE=5 captures the end test milepost.  Other record types can occur between the start and end records - MCODE=7, the movement portion of a defect record, is the most obvious example.  So creating a 460 line is more of a cursor/loop operation:
start at the first 0 record; set started flag; store track and milepost to variables; movenext; if MCODE=7, set defect flag and store DEFID to variable; if MCODE=5, store milepost to variable; write line; clear started flag; if defect flag, AddDefect(DEFID); movenext.  All-movements-in-one-place is not required here, as no test occurs across a location boundary.

The '400 defect record is the easiest, as DEFID is a unique identifier.

I hope I've been clear, as it's pushing 02:30 Friday morning, I've been at this more hours than my butt cares to remember, and my eyes are getting uncooperative.  Thank you ahead of time for any effort you can spare to put into this.  I've got a meeting in the morning and probably won't be able to get to my desk until after lunch (eastern, US), but when I get here I'll be ready to rock and roll!

Ginny
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Good Morning Ginny;

To your statement, "(From the immediate window I run: ?createUniqueLNNUMlist("D:\Program Files\DCS.NET\Data\80018210.xml"). a console window opens, but nothing is displayed, and the return at the immediate window is "Unrecognized escape sequence".)", The reason for the error message "Unrecognized escape sequence" is due to the invalid escape sequence in this part of the statement, "D:\Program Files\DCS.NET\Data\80018210.xml", any time you place a \ in a string constant the next character must be a valid escape character. In the string the following characters are not escape sequence, \P, \D, and \8. To correct the issue you have two ways to do it, 1. is to place the character @ just before the string start like so, @"D:\Program Files\DCS.NET\Data\80018210.xml" or 2. to escape all the invalid escapes so that they do not get translate them the string would look like this, "D:\\Program Files\\DCS.NET\\Data\\80018210.xml", when the compiler/interpiter reads either string now it will translate it to, "D:\Program Files\DCS.NET\Data\80018210.xml". If you use version 1 above then the compile will not covert any escape sequence.

I modified your code below it should work without using the Immediate Window.

Fernando
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a List of String variable to hold the return value and
            // call the method to fill it.
            List<String> lnnumList = createUniqueLNNUMlist(""D:\\Program Files\\DCS.NET\\Data\\80018210.xml"");

            // When creating a Console program during development put this ReadLine before
            // the program exits so that the Console window does not close until you are
            // ready then press the return key on the keyboard
            Console.ReadLine();
        }

        // If this function is NOT made static then an instance of the class containing the method
        // will need to be created. So I am making this method static so that we can just us it in Main.
        public static List<string> createUniqueLNNUMlist(string filename)
        {
            // Load the XML document 
            XDocument xdoc = XDocument.Load(filename);
            // Get reference to root element
            XElement root = xdoc.Root;

            // Get all the unique LNNUMs in the documents
            List<string> uniqueLNNUMs = (from LNNUM in root.Descendants("LNNUM")
                                         select LNNUM.Value).Distinct().ToList();

            // Display all the unique LNNUMs
            foreach (var lnnum in uniqueLNNUMs)
            {
                Console.WriteLine(lnnum);
            }
            return uniqueLNNUMs;
        }
    }
}

Open in new window

Fernando SotoRetired
Distinguished Expert 2017

Commented:
Ginny, would it be possible to upload the VBA program file you are to convert to C#, maybe I can see where all these numbers are coming from? For example this line:

'450,7100,35.5,3,SRS800,1,20100701, RB,0050,0042,0467,0017,0000,0042,0000,0617,0,0,0,"test report, multiple lines, at least one duplic"'

I could not find the the first value 450 anywhere in the XML document and some of the other values there as well. I did find the text at the end of the line but even that seems to be truncated.

Fernando

Author

Commented:
450, 460, and 400 do not exist in the data, but are customer-specific prefixes indicating the type and format of the data to follow.

I've attached an abbreviated spec for the upload file, comments in blue relate to Nodes/Elements in the xml report.
My VBA function is embedded in, and depends on the output from, proprietary software that I can't attach here.  I'll see if I can pull out any meaningful bits that would be helpful.

Ginny
UploadSpec-v1-4.doc
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Hi Ginny;

I downloaded the document and printed it and will be looking at it tonight to see what I can come up with. Will post as soon as I have anything.

Just wanted top let you know I did not forget.

Fernando
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Hi Ginny;

In the File Transmission Structure document it defines a field name "Srs800", Car ID, which I can not find in the XML but appears in your results on post ID: 33121006 and has a value of SRS800 for both 450 records. How do I figure out this field.

Fernando
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Hi Ginny;

The field Defects states in the document that it is equal to "This value will be equal to the total no. of all 400 records in this particular 450", do not really know what that means. I can not look at the XML document and find all the 400's in the record 450. The field after that is called Data but does not have XML node name.

I guess what I would need is to get something that takes each record type , fields, and indicate where in the XML document to get the data from and if any special criteria needs to be aware of.

The code snippet shows how to get the first 6 fields. The Car ID is set to ?????? because do not know how to get it. Also Defect is currently showing wrong value for Defects, not sure how to get it.

Fernando
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;

namespace ConsoleApplication4
{
    class Program
    {
        private static XDocument xdoc;
        private static XElement root;

        static void Main(string[] args)
        {
            // Load the XML document 
            xdoc = XDocument.Load("80018210.xml");
            // Get reference to root element
            root = xdoc.Root;

            // Get all the unique LNNUMs in the documents
            List<String> lnnumList = createUniqueLNNUMlist();
            // Get 450 record types
            List<RecordType450> R450 = Get450Record();
            foreach (RecordType450 rec in R450)
            {
                Console.WriteLine(rec.ToString());
            }

            // Is here to stop the program from terminating press enter to continue
            Console.ReadLine();
        }

        // If this function is NOT made static then an instance of the class containing the method
        // will need to be created. So I am making this method static so that we can just us it in Main.
        public static List<string> createUniqueLNNUMlist()
        {
            // Get all the unique LNNUMs in the documents
            List<string> uniqueLNNUMs = (from LNNUM in root.Descendants("LNNUM")
                                         select LNNUM.Value).Distinct().ToList();

            // Display all the unique LNNUMs
            foreach (var lnnum in uniqueLNNUMs)
            {
                Console.WriteLine(lnnum);
            }
            return uniqueLNNUMs;
        }

        public static List<RecordType450> Get450Record()
        {
            //List<String> results = new List<string>();
            List<RecordType450> R450 = 
                (from loc in root.Descendants("Location").Elements("Movements").Elements("Movement")
                 where loc.Element("MCODE").Value == "5"
                 group loc by loc.Parent.Parent.Element("LNNUM").Value into locGroup
                 select new RecordType450
                 {
                     LNNUM = locGroup.Key,
                     EMILES = Math.Round(locGroup.Sum( em => Decimal.Parse( em.Element("EMILES").Value)), 1),
                     EndTest = locGroup.Select( s => s.Parent.Parent.Elements("Movement")).Count(),
                     CarID = "??????",
                     Defects = root.Descendants("Locations").Elements("Location").Where(mc => mc.Element("Movements").Element("Movement").Element("MCODE").Value == "5").Elements("Defects").Elements("Defect").Count()
                 }).ToList();
            return R450;
        }
    }

    // The query in method Get450Record() uses this class to create a return type of the query
    public class RecordType450
    {
        private String type = "450";
        public String Type { get { return type; } }
        public String LNNUM { get; set; }
        public Decimal EMILES { get; set; }
        public Int32 EndTest { get; set; }
        public String CarID { get; set; }
        public Int32 Defects { get; set; }

        public override string ToString()
        {
            return String.Format("{0},{1},{2},{3},{4}", Type, LNNUM, EMILES, CarID, EndTest);
        }
    }
}

Open in new window

Author

Commented:
Fernando,

*chagrin*

Yes, now that you have pointed it out, I see that carnum is not included as a discreet value in the xml version of the data schema.  I assume that is because it can be substringed out of the RPTID, where it is always the first 3 digits.  But if that is the logic, why have discreet elements for RPTNUM (Julian day) or RPTSFX (for more than one report, i.e. customer, in a day), which are the other parts of the RPT/RPTID construct?

A bit of background...I wrote an Access/Office 97 app for collecting/reporting test data that has been in use across the fleet for the last 12 years.  The DCS (data collection system, original, uh?) stores records in an Access database on the test vehicle.  At the end of the day, the Transmit function converts table records to text files, that are compressed into a zip, attached to an email and sent to an automated mailbox.  Until about 2 years ago, a manually-initiated 'morning processing' routine stripped the attachments, extracted the text files from the zip and appended the data to the DMS (data management system) on an in-house fileserver.  Over the last couple of years, the DMS has been migrated to a set of SQLserver databases and scheduled 'Jobs' do the processing.  Now it's time for the DCS to get a facelift.  Unfortunately, I did not have the skill set or the time to be the chief architect of this upgrade (I'm working on the skill set.  I have a request in for 6 hours to be added to the length of each day, but I've had no response to that yet.)  But since nobody else knows the data as well as I do, I've become the primary beta-tester and interface consultant.
The new DCS.NET stores data on the car in the xml format.  That data is uploaded to a web service and that service parses the data into the text files that are zipped, attached and emailed - this is where the new meets the old, so to speak.  Eventually the service will feed directly to the SQL databases, but one step at a time.
In order to create the customer data upload file that I provided as an example, I uploaded the xml to the service, then grabbed the resulting email out of the inbox, stripped the attachment and imported the text file data into a local copy of the original DCS (confused yet?).  This all goes toward explaining why I didn't realize that the <CARNUM> element I was pointing you to wasn't there.

Sorry.

Ginny
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Hi Ginny;

To your statement, "I assume that is because it can be substringed out of the RPTID, where it is always the first 3 digits.", OK the below code has been updated to get the Car ID from the RPTID element. One thing in the File  Transmission Structure it states that the length of this field is always 6 characters long and gives examples as SRS804, 975125?

Fernando
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;

namespace ConsoleApplication4
{
    class Program
    {
        private static XDocument xdoc;
        private static XElement root;

        static void Main(string[] args)
        {
            // Load the XML document 
            xdoc = XDocument.Load("80018210.xml");
            // Get reference to root element
            root = xdoc.Root;

            // Get all the unique LNNUMs in the documents
            List<String> lnnumList = createUniqueLNNUMlist();
            // Get 450 record types
            List<RecordType450> R450 = Get450Record();
            foreach (RecordType450 rec in R450)
            {
                Console.WriteLine(rec.ToString());
            }

            // Is here to stop the program from terminating press enter to continue
            Console.ReadLine();
        }

        // If this function is NOT made static then an instance of the class containing the method
        // will need to be created. So I am making this method static so that we can just us it in Main.
        public static List<string> createUniqueLNNUMlist()
        {
            // Get all the unique LNNUMs in the documents
            List<string> uniqueLNNUMs = (from LNNUM in root.Descendants("LNNUM")
                                         select LNNUM.Value).Distinct().ToList();

            // Display all the unique LNNUMs
            foreach (var lnnum in uniqueLNNUMs)
            {
                Console.WriteLine(lnnum);
            }
            return uniqueLNNUMs;
        }

        public static List<RecordType450> Get450Record()
        {
            //List<String> results = new List<string>();
            List<RecordType450> R450 = 
                (from loc in root.Descendants("Location").Elements("Movements").Elements("Movement")
                 where loc.Element("MCODE").Value == "5"
                 group loc by loc.Parent.Parent.Element("LNNUM").Value into locGroup
                 select new RecordType450
                 {
                     LNNUM = locGroup.Key,
                     EMILES = Math.Round(locGroup.Sum( em => Decimal.Parse( em.Element("EMILES").Value)), 1),
                     EndTest = locGroup.Select( s => s.Parent.Parent.Elements("Movement")).Count(),
                     CarID = locGroup.FirstOrDefault().Parent.Parent.Parent.Parent.Element("RPTID").Value.Substring(0, 3),
                     Defects = root.Descendants("Locations").Elements("Location").Where(mc => mc.Element("Movements").Element("Movement").Element("MCODE").Value == "5").Elements("Defects").Elements("Defect").Count()
                 }).ToList();
            return R450;
        }
    }

    // The query in method Get450Record() uses this class to create a return type of the query
    public class RecordType450
    {
        private String type = "450";
        public String Type { get { return type; } }
        public String LNNUM { get; set; }
        public Decimal EMILES { get; set; }
        public Int32 EndTest { get; set; }
        public String CarID { get; set; }
        public Int32 Defects { get; set; }

        public override string ToString()
        {
            return String.Format("{0},{1},{2},{3},{4}", Type, LNNUM, EMILES, CarID, EndTest);
        }
    }
}

Open in new window

Author

Commented:
Fernando

First, I didn't finish answering your question...the "srs" portion of the car number is a customer request, prefixing the number to distinguish our cars (Sperry Rail Service, look us up, top of our field!) from the test vehicles of our competitors.

Second, an explanation why you've been working on this harder today than I have.  Mid-afternoon today, one of my roommates slipped on some steps while delivering a case of wine and broke her ankle, badly.  She was admitted to the hospital and had surgery a couple of hours ago, adding a plate and 9 screws to hold her foot on.  It was my job to get her van back to the depot and help transfer the load to another driver before getting it home...which was much better than sitting in the hospital waiting room looking worried, which was the other roommate's job!  It's gonna be a helluva holiday weekend.

re your latest post
400 record = a Defect node (mixed with some Movement elements, where Movement.DEFID=Defect.DEFID)
450 record = based on all Location nodes with the same LNNUM value
so the total number of Defect nodes across all Locations with the same LNNUM is equal to the total 400 records for a specific 450 record.

Ginny
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Hi Ginny;

To your statement, "srs portion of the car number is a customer request, prefixing the number to distinguish our cars top of our field!) from the test vehicles of our competitors.", OK but where in the XML document can I tell if it  is going to be SRS your can or a customer car?

Sorry to hear that you roommate broke her ankle and I hope that she has a easy recovery. Did you lose the whole case of wine, hope not.

I will try to understand the rest of your last post tomorrow/today going to head to bed, I am on the east cost.

Talk later;
Fernando
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Hi Ginny;

I have been working on this all morning and in my opinion the way this XML file is laid out is difficult to follow and understand to know the relationship between records / nodes and what is needed as an end result. I do not want to hold you up on your project and not be able to meet your needs. I will be willing to answer any question that I can help on I will also be willing to do some research on XML questions you may also have but I can tell you right now that I will not be able to come up with something on my own without the intricate knowledge of how this all works.  

Fernando

Author

Commented:
Fernando,

In the zip, Dcs97Db is the source the xml mostly mimics.  The Operation, Crew, NonCrew, Equip, and PO (field Purchase Orders) tables describe the entire work day, regardless of customer.  Report (one or more per day), Location (one or more per Report), Movement (2 or more per Location), Defect (one-to-one with a Movement) and RDE (Rail Detection Exception, 0 or more per Location) describe work performed for a customer.
Dcs97Lst contains the lookup tables for Crew Names and acceptable Defect codes, TrackID (TID) codes, etc.  I've also included a query lookup table and the code module that makes the upload file.  I'm working on a map of the xml as to what elements relate to values required in the upload file.

My roommate was allowed to come home from the hospital today.  She'll be off her feet 8-12 weeks!  Thanks for the well-wishes.

Ginny

Dcs97BN10.zip

Author

Commented:
Fernando,

How do I format RDate, where RDate = locGroup.FirstOrDefault().Parent.Parent.Parent.Parent.Element("DATE").Value,
to "yyyyMMdd"?

Thanks

Ginny

Author

Commented:
RecordType450:

'450,            - Single-quote and Location record-type code, hardwired
7100,            - Location.LNNUM
35.5,            - Sum(Movement.EMILES) WHERE Location.LNNUM=7100 AND Movement.MCODE=5
3,                  - Count* WHERE Location.LNNUM=7100 AND Movement.MCODE=5
SRS800,            - SRS is hardwired (all source data will be from sperry cars), & Mid(Report.RPT,1,3)
1,                  - Count* WHERE Location.LNNUM=7100 AND Movement.MCODE=7
20100701,      - Format(Report.DATE, "yyyymmdd")
 RB,            - SELECT Crewids.INIT FROM Crew INNER JOIN Crewids ON Crew.EMPID = Crewids.EmpID WHERE Crew.TITLE="CO"; pad to 3 chars
0050,            - Sum(Movement.CHRGTIME) WHERE Location.LNNUM=7100 AND Movement.MCODE=3 AND Movement.CHRGCODE=1 AND Movement.NTDT=True
0042,            - Sum(Movement.CHRGTIME) WHERE Location.LNNUM=7100 AND Movement.MCODE=3 AND Movement.CHRGCODE=1 AND Movement.NTDT=False and Movement.MVX3=False
0467,            - Sum(Movement.CHRGTIME) WHERE Location.LNNUM=7100 AND Movement.MCODE=5 AND Movement.CHRGCODE=1
0017,            - Sum(Movement.CHRGTIME) WHERE Location.LNNUM=7100 AND Movement.MCODE=4 AND Movement.CHRGCODE=1 AND Movement.TrackID="RD"
0000,            - Sum(Movement.CHRGTIME) WHERE Location.LNNUM=7100 AND Movement.MCODE=4 AND Movement.CHRGCODE=1 AND Movement.TrackID<>"RD"
0042,            - Sum(Movement.CHRGTIME) WHERE Location.LNNUM=7100 AND Movement.MCODE=3 AND Movement.CHRGCODE=1 AND Movement.NTDT=False and Movement.MVX3=True
0000,            - Sum(Movement.CHRGTIME) WHERE Location.LNNUM=7100 AND Movement.MCODE=3 AND Movement.CHRGCODE=3
0617,            - Sum of the previous 7 sums
0,                  - (int) Location.LCX2, from the 1st Location Where Location.LNNUM=7100 only
0,                  - (decimal) Location.LCX5, from the 1st Location Where Location.LNNUM=7100 only
0,                  - (byte) Location.LCX1, from the 1st Location Where Location.LNNUM=7100 only
"test..."'      - Report.EOSCOM, 50 characters max, including dbl-quotes, line ends with a single-quote
Crewids.xml
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Ginny, All you need to do to get RDate in the format of "yyyyMMdd" is to rap the right side of your statement with a DateTime.Parse(...) and apply the ToString("yyyyMMdd") method as shown in the code snippet below.
RDate = DateTime.Parse(locGroup.FirstOrDefault().Parent.Parent.Parent.Parent.Element("DATE").Value).ToString("yyyyMMdd")

Open in new window

Author

Commented:
Thanks for the DateTime.Parse answer.  I should've thought of that.

Since you reference locGroup for a Count of EndTest, I guess it only includes the 3 MCODE=5 (end test) records.
(I assume the construct "s =>" is equivalent to the SQL "AS" keyword, indicating an alias.)
There are 2 ways to count defects - either count the Defect nodes for the group or count Movement.MCODE=7 for the group.  Can you explain what this line is trying to do?
"Defects = root.Descendants("Locations").Elements("Location").Where(mc => mc.Element("Movements").Element("Movement").Element("MCODE").Value == "5").Elements("Defects").Elements("Defect").Count(),"?  The Defects Node is an element of the Location node, not the Movements node.
How do I create a group against which I could run queries like:
"Sum(Movement.CHRGTIME) WHERE Location.LNNUM=7100 AND Movement.MCODE=3 AND Movement.CHRGCODE=1 AND Movement.NTDT=False and Movement.MVX3=False"?

I'm sorry if I left the impression that I was leaving you to do all the work - that is not the case.  I did not expect to become a care-giver at this time.  I've farmed the crippled roommate out to her sister's for the rest of the holiday weekend, so I can get back to it.  Any time you can spare from your own holiday weekend to counteract my ignorance would be greatly appreciated.

Ginny


Author

Commented:
Fernando,

There are 2 occasions in creating the upload file where lookup functions are required, the chief operator's initials (where Crew.Empid = Crewids.EmpID) and the defect code (where Defect.DTYPE = lstDefs.DTYPE).  Crewids and lstDefs are separate, supporting xml files.  I attached a copy of Crewids.xml to a previous post.  If you could give me an example for the initials, I'll figure it out for the dcode.

Thanks

Ginny
Retired
Distinguished Expert 2017
Commented:
Hi Ginny;

To your post ID: 33133476
To your statement, "(I assume the construct "s =>" is equivalent to the SQL "AS" keyword, indicating an alias.)", No it is not. The symbol => in C# is for Lambda expression, see link:

Lambda Expressions (C# Programming Guide)
http://msdn.microsoft.com/en-us/library/bb397687.aspx

I have changed the design of the code to use individual queries for each field because it is to difficult to design a single query. The queries are using Lambda expression. The 450 record should be complete except for the CrewID don't undestand you info on post ID: 33133524.

I will be in and out the rest of today and tomorrow I need to do some chores. You should be able to do the others using this as a sample.

Fernando
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;

namespace ConsoleApplication5
{
    class Program
    {

        private static XDocument xdoc;
        private static XElement root;
        
        static void Main(string[] args)
        {
            // Load the XML document 
            xdoc = XDocument.Load("80018210.xml");
            // Get reference to root element
            root = xdoc.Root;

            // Get all the unique LNNUMs in the documents
            List<String> lnnumList = createUniqueLNNUMlist();
            List<String> Rpt = new List<string>();

            foreach (String lnnum in lnnumList)
            {
                RecordType450 r450 = Get450Record(lnnum);
                Rpt.Add(r450.ToString());
                List<String> r460 = Get460Record(lnnum);
                Rpt.AddRange(r460);
                RecordType400 r400 = Get400Record(lnnum);
                Rpt.Add(r400.ToString());
            }

            // Use / print the report in Rpt

            Console.ReadLine();
        }

        // If this function is NOT made static then an instance of the class containing the method
        // will need to be created. So I am making this method static so that we can just us it in Main.
        public static List<string> createUniqueLNNUMlist()
        {
            // Get all the unique LNNUMs in the documents
            List<string> uniqueLNNUMs = (from LNNUM in root.Descendants("LNNUM")
                                         select LNNUM.Value).Distinct().ToList();
            return uniqueLNNUMs;
        }

        public static RecordType450 Get450Record(String lnnum)
        {
            RecordType450 r450 = new RecordType450();
            IEnumerable<XElement> Movement = root.Descendants("Movement");

            r450.LNNUM = lnnum;
            r450.EMILES = Math.Round(Movement.Where(m => m.Parent.Parent.Element("LNNUM").Value == lnnum && 
                m.Element("MCODE").Value == "5").Select(m => m).Sum(m => Decimal.Parse(m.Element("EMILES").Value)), 1);
            r450.EndTest = Movement.Where(m => m.Parent.Parent.Element("LNNUM").Value == lnnum &&
                m.Element("MCODE").Value == "5").Count();
            r450.CarID = "SRS" + root.Element("Reports").Element("Report").Element("RPT").Value.Substring(0, 3);
            r450.Defects = Movement.Where(m => m.Parent.Parent.Element("LNNUM").Value == lnnum &&
                m.Element("MCODE").Value == "7").Count();
            r450.RDate = DateTime.Parse(root.Element("Reports").Element("Report").Element("DATE").Value).ToString("yyyyMMdd");
            r450.Crews = root.Element("Crews").Value.PadRight(3);
            r450.OtherTime = Movement.Where(m => m.Parent.Parent.Element("LNNUM").Value == lnnum &&
                m.Element("MCODE").Value == "3" && m.Element("CHRGCODE").Value == "1" && m.Element("NTDT").Value == "True")
                .Sum(m => Int32.Parse(m.Element("CHRGTIME").Value));
            r450.TrainDelay = Movement.Where(m => m.Parent.Parent.Element("LNNUM").Value == lnnum &&
                m.Element("MCODE").Value == "3" && m.Element("CHRGCODE").Value == "1" && m.Element("NTDT").Value == "False" && m.Element("MVX3").Value == "False")
                .Sum(m => Int32.Parse(m.Element("CHRGTIME").Value));
            r450.TestTime = Movement.Where(m => m.Parent.Parent.Element("LNNUM").Value == lnnum &&
                m.Element("MCODE").Value == "5" && m.Element("CHRGCODE").Value == "1")
                .Sum(m => Int32.Parse(m.Element("CHRGTIME").Value));
            r450.RunlightTime = Movement.Where(m => m.Parent.Parent.Element("LNNUM").Value == lnnum &&
                m.Element("MCODE").Value == "4" && m.Element("CHRGCODE").Value == "1" && m.Element("TRACKID").Value == "RD")
                .Sum(m => Int32.Parse(m.Element("CHRGTIME").Value));
            r450.TrackTravel = Movement.Where(m => m.Parent.Parent.Element("LNNUM").Value == lnnum &&
                m.Element("MCODE").Value == "4" && m.Element("CHRGCODE").Value == "1" && m.Element("TRACKID").Value != "RD")
                .Sum(m => Int32.Parse(m.Element("CHRGTIME").Value));
            r450.SupportDelay = Movement.Where(m => m.Parent.Parent.Element("LNNUM").Value == lnnum &&
                m.Element("MCODE").Value == "3" && m.Element("CHRGCODE").Value == "1" && m.Element("NTDT").Value == "False" && m.Element("MVX3").Value == "True")
                .Sum(m => Int32.Parse(m.Element("CHRGTIME").Value));
            r450.RepairTime = Movement.Where(m => m.Parent.Parent.Element("LNNUM").Value == lnnum &&
                m.Element("MCODE").Value == "3" && m.Element("CHRGCODE").Value == "3")
                .Sum(m => Int32.Parse(m.Element("CHRGTIME").Value));
            r450.TotalHours = r450.OtherTime + r450.TrainDelay + r450.TestTime + r450.RunlightTime + r450.TrackTravel + r450.SupportDelay + r450.RepairTime;
            r450.FreqDays = Int32.Parse(root.Descendants("Location").Where(l => l.Element("LNNUM").Value == lnnum).First().Element("LCX2").Value);
            r450.AvgMile = Decimal.Parse(root.Descendants("Location").Where(l => l.Element("LNNUM").Value == lnnum).First().Element("LCX5").Value);
            r450.FreqStatus = (root.Descendants("Location").Where(l => l.Element("LNNUM").Value == lnnum).First().Element("LCX1").Value == String.Empty) ? "0" : root.Descendants("Location").Where(l => l.Element("LNNUM").Value == lnnum).First().Element("LCX1").Value;
            r450.Remarks = root.Element("Reports").Element("Report").Element("EOSCOM").Value.Substring(0, 50);

            return r450;
        }

        public static List<String> Get460Record(String lnnum)
        {
            List<String> r460 = new List<string>();
            r460.Add("'Report, 460, Needs, To, Be, Implemented'");
            r460.Add("'Report, 460, Needs, To, Be, Implemented'");

            // Add each 460 record to the r460 list

            return r460;
        }

        public static RecordType400 Get400Record(String lnnum)
        {
            RecordType400 r400 = new RecordType400();

            // Implement like Get450Record

            return r400;
        }
    }

    // The query in method Get450Record() uses this class to create a return type of the query
    public class RecordType450
    {
        private String type = "450";
        public String Type { get { return type; } }
        public String LNNUM { get; set; }
        public Decimal EMILES { get; set; }
        public Int32 EndTest { get; set; }
        public String CarID { get; set; }
        public Int32 Defects { get; set; }
        public String RDate { get; set; }
        public String Crews { get; set; }
        public Int32 OtherTime { get; set; }
        public Int32 TrainDelay { get; set; }
        public Int32 TestTime { get; set; }
        public Int32 RunlightTime { get; set; }
        public Int32 TrackTravel { get; set; }
        public Int32 SupportDelay { get; set; }
        public Int32 RepairTime { get; set; }
        public Int32 TotalHours { get; set; }
        public Int32 FreqDays { get; set; }
        public Decimal AvgMile { get; set; }
        public String FreqStatus { get; set; }
        public String Remarks { get; set; }

        public override string ToString()
        {
            return String.Format("'{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18},{19}'", 
                Type, LNNUM, EMILES, EndTest, CarID, EndTest, RDate, Crews, OtherTime.ToString().PadLeft(4, '0'),
                TrainDelay.ToString().PadLeft(4, '0'), TestTime.ToString().PadLeft(4, '0'), RunlightTime.ToString().PadLeft(4, '0'),
                TrackTravel.ToString().PadLeft(4, '0'), SupportDelay.ToString().PadLeft(4, '0'),
                RepairTime.ToString().PadLeft(4, '0'), TotalHours.ToString().PadLeft(4, '0'), FreqDays, AvgMile,
                FreqStatus, Remarks);
        }
    }

    // The query in method Get460Record() uses this class to create a return type of the query
    public class RecordType460
    {
        // Need to implement
    }

    // The query in method Get400Record() uses this class to create a return type of the query
    public class RecordType400
    {
        // Need to implement

        public override string ToString()
        {
            return "'Report, 400, Needs, To, Be, Implemented'";
        }
    }
}

Open in new window

Author

Commented:
Fernando went above and beyond, gave plenty of information and examples from which to learn, despite the gaping holes in my knowledge and difficult time constraints.  Kudos!
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Thank you, always glad to help. Hope all went well with the project.  ;=)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial