locked
Issue when writing XML data to a datatable / gridview RRS feed

  • Question

  • User647135053 posted

    I have an API feed from a Web Services call with our LMS, condensed sample code is below. <a:RosterAddedUserView> is duplicated for each user that appears on the roster and <a:PartAttendanceView> is duplicated for each piece of the course schedule (Day 1, Day 2, etc.).

    <applicationApi xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
      <Validations />
      <status>200</status>
      <timeStamp>2017-04-26T17:03:11+0000</timeStamp>
      <totalRecords>1</totalRecords>
      <data xmlns:a="www.applicationurl.com/Services">
        <a:SessionRosterResponseView>
          <a:Roster>
            <a:TotalSeat>20</a:TotalSeat>
            <a:TotalSeatAvaliable>4</a:TotalSeatAvaliable>
            <a:PendingUser />
            <a:AddedUser>
              <a:RosterAddedUserView>
                <a:UserId>12345</a:UserId>
                <a:UserFullName>Smith, John</a:UserFullName>
                <a:OUs>
                  <a:OrganizationUnitView>
                    <a:Name>EMPLOYEE</a:Name>
                    <a:TypeName>Division</a:TypeName>
                    <a:RelatedOUs i:nil="true" />
                  </a:OrganizationUnitView>
                </a:OUs>
                <a:Locator>LocNum</a:Locator>
                <a:Attendance>
                  <a:PartAttendanceView>
                    <a:PartName>FirstDay</a:PartName>
                    <a:UserAttendance>true</a:UserAttendance>
                  </a:PartAttendanceView>
                  <a:PartAttendanceView>
                    <a:PartName>SecondDay</a:PartName>
                    <a:UserAttendance>true</a:UserAttendance>
                  </a:PartAttendanceView>
                  <a:PartAttendanceView>
                    <a:PartName>LastDay</a:PartName>
                    <a:UserAttendance>false</a:UserAttendance>
                  </a:PartAttendanceView>
                </a:Attendance>
                <a:Score i:nil="true" />
                <a:Passed>true</a:Passed>
                <a:Status>Incomplete</a:Status>
                <a:SessionCompletionDate>4/14/2017</a:SessionCompletionDate>
              </a:RosterAddedUserView>
              <a:RosterAddedUserView>
                <a:UserId>67890</a:UserId>
                <a:UserFullName>Doe, Jane</a:UserFullName>
                <a:OUs>
                  <a:OrganizationUnitView>
                    <a:Name>EMPLOYEE</a:Name>
                    <a:TypeName>Division</a:TypeName>
                    <a:RelatedOUs i:nil="true" />
                  </a:OrganizationUnitView>
                </a:OUs>
                <a:Locator>LocNum</a:Locator>
                <a:Attendance>
                  <a:PartAttendanceView>
                    <a:PartName>FirstDay</a:PartName>
                    <a:UserAttendance>false</a:UserAttendance>
                  </a:PartAttendanceView>
                  <a:PartAttendanceView>
                    <a:PartName>SecondDay</a:PartName>
                    <a:UserAttendance>false</a:UserAttendance>
                  </a:PartAttendanceView>
                  <a:PartAttendanceView>
                    <a:PartName>LastDay</a:PartName>
                    <a:UserAttendance>true</a:UserAttendance>
                  </a:PartAttendanceView>
                </a:Attendance>
                <a:Score i:nil="true" />
                <a:Passed>true</a:Passed>
                <a:Status>Incomplete</a:Status>
                <a:SessionCompletionDate>4/14/2017</a:SessionCompletionDate>
              </a:RosterAddedUserView>
            </a:AddedUser>
            <a:Comments />
            <a:TotalPendingUsers>0</a:TotalPendingUsers>
            <a:TotalAddedUsers>16</a:TotalAddedUsers>
          </a:Roster>
          <a:Result>Success</a:Result>
          <a:Reason i:nil="true" />
        </a:SessionRosterResponseView>
      </data>
    </applicationApi>

    I need to be able to generate a table view of this data including the following fields: UserID, UserFullName and attendance. I'd like the attendance to be columns with each record.

    Example:

    UserId

    UserFullName

    FirstDay

    SecondDay

    LastDay

    12345

    Smith, John

    true

    true

    false

    67890

    Doe, Jane

    false

    false

    true

    I've been able to successfully pull the UserID and UserFullName, and have been able to pull in the attendance column headings based on <a:PartName>, but when I attempt to fill with the <a:UserAttendance> value I am not getting an inaccurate result. I've attempted this a few different ways, but it appears that currently I am getting the last user's attendance record for all users.

    Current Results:

    UserId

    UserFullName

    FirstDay

    SecondDay

    LastDay

    12345

    Smith, John

    false

    false

    true

    67890

    Doe, Jane

    false

    false

    true

    Any help with troubleshooting the code would be much appreciated.  Below is the code I am using that is attached to a button on a Windows Form that populates a Data Grid View.  I am guessing I'm doing something out of place, but I can not figure out what the issue is with my code.

    private void btnReadXML_Click(object sender, EventArgs e)
    {
    //Load XML Data and create Namespace Manager
    XmlDocument xmldoc = new XmlDocument();
    xmldoc.Load(@"Path_To_XML.xml");
    XmlNamespaceManager nsmgr = new XmlNamespaceManager(xmldoc.NameTable);
    nsmgr.AddNamespace("a", "www.applicationurl.com/Services");
    
    //Create data table and add UserID and UserFullName columns
    DataTable dt = new DataTable();
    dt.Columns.Add("UserId", typeof(string));
    dt.Columns.Add("UserFullName", typeof(string));
    
    //Populate UserID and UserFullName columns
    XmlNodeList userList = xmldoc.SelectNodes("/applicationApi/data/a:SessionRosterResponseView/a:Roster/a:AddedUser/*", nsmgr);
    foreach (XmlNode user in userList)
    {
        DataRow dtrow = dt.NewRow();
        dtrow["UserId"] = user["a:UserId"].InnerText;
        dtrow["UserFullName"] = user["a:UserFullName"].InnerText;
    
        //Add PartName columns for attendance records (only checks the first occurance of RosterAddedUserView - all users will have the same Parts.)
        XmlNodeList partList = xmldoc.SelectNodes("/applicationApi/data/a:SessionRosterResponseView/a:Roster/a:AddedUser/a:RosterAddedUserView[1]/a:Attendance/*", nsmgr);
        foreach (XmlNode part in partList)
        {
            if (!dt.Columns.Contains(part["a:PartName"].InnerText;))
            {
                dt.Columns.Add(part["a:PartName"].InnerText;, typeof(string));
            }
        }
    
        //Populate PartName columns with User Attendance values
        XmlNodeList attendList = xmldoc.SelectNodes("/applicationApi/data/a:SessionRosterResponseView/a:Roster/a:AddedUser/a:RosterAddedUserView/a:Attendance/*", nsmgr);
        foreach (XmlNode attend in attendList)
        {
            dtrow[attend["a:PartName"].InnerText] = attend["a:UserAttendance"].InnerText;
        }
    
    //Add the created rows to the data table
        dt.Rows.Add(dtrow);
    }
    
    //Set DataSource for dataGridView1 to the data table.
    dataGridView1.DataSource = dt;
    }

    Thursday, April 27, 2017 7:20 PM

All replies

  • User647135053 posted

    This has been resolved as it appears I was just simply overthinking it.  Updated code below.

    private void btnReadXML_Click(object sender, EventArgs e)
    {
    //Get count of users on Roster and count of Parts
    var userCount = xmldoc.SelectNodes("/applicationApi/data/a:SessionRosterResponseView/a:Roster/a:AddedUser/*", nsmgr).Count;
    var partCount = xmldoc.SelectNodes("/applicationApi/data/a:SessionRosterResponseView/a:Roster/a:AddedUser/a:RosterAddedUserView[1]/a:Attendance/*", nsmgr).Count;
    
    
    //Load XML Data and create Namespace Manager
    XmlDocument xmldoc = new XmlDocument();
    xmldoc.Load(@"Path_To_XML.xml");
    XmlNamespaceManager nsmgr = new XmlNamespaceManager(xmldoc.NameTable);
    nsmgr.AddNamespace("a", "www.applicationurl.com/Services");
    
    //Create data table and add UserID and UserFullName columns
    DataTable dt = new DataTable();
    dt.Columns.Add("UserId", typeof(string));
    dt.Columns.Add("UserFullName", typeof(string));
    
    
    
    //Add PartName columns for attendance records (only checks the first occurance of RosterAddedUserView - all users will have the same Parts.)
    XmlNodeList partList = xmldoc.SelectNodes("/applicationApi/data/a:SessionRosterResponseView/a:Roster/a:AddedUser/a:RosterAddedUserView[1]/a:Attendance/*", nsmgr);
    foreach (XmlNode part in partList)
    {
        if (!dt.Columns.Contains(part["a:PartName"].InnerText))
        {
            dt.Columns.Add(part["a:PartName"].InnerText, typeof(string));
        }
    } 
    
    //Add Completed and Passed columns
    dt.Columns.Add("Completed", typeof(string));
    dt.Columns.Add("Passed", typeof(string));
    
    int x = 1;
    do
    {
        DataRow dtrow = dt.NewRow();
        dtrow["UserId"] = xmldoc.SelectSingleNode(".//a:RosterAddedUserView[" + x + "]/a:UserId", nsmgr).InnerText;
        dtrow["UserFullName"] = xmldoc.SelectSingleNode(".//a:RosterAddedUserView[" + x + "]/a:UserFullName", nsmgr).InnerText;
    
        int y = 1;
        do
        {
            dtrow[xmldoc.SelectSingleNode(".//a:RosterAddedUserView[" + x + "]/a:Attendance/a:PartAttendanceView[" + y + "]/a:PartName", nsmgr).InnerText] = xmldoc.SelectSingleNode(".//a:RosterAddedUserView[" + x + "]/a:Attendance/a:PartAttendanceView[" + y + "]/a:UserAttendance", nsmgr).InnerText;
            y++;
        } while (y <= partCount);
    
        dtrow["Completed"] = xmldoc.SelectSingleNode(".//a:RosterAddedUserView[" + x + "]/a:Status", nsmgr).InnerText;
        dtrow["Passed"] = xmldoc.SelectSingleNode(".//a:RosterAddedUserView[" + x + "]/a:Passed", nsmgr).InnerText;
        dt.Rows.Add(dtrow);
        x++;
    } while (x <= userCount);
        dataGridView1.DataSource = dt;
    }

    Thursday, April 27, 2017 8:25 PM