I have following XML collection of students as shown below.
<stdXmlFormat version="1.0">
<students>
<field id="ClassId">CB0012</field>
<field id="ClassName">CBSE-12</field>
<student id="123">
<field id="SFirstNm">Ram</field>
<field id="SLastNm">Raju</field>
<field id="RollNumber">001</field>
</student>
<student id="124">
<field id="SFirstNm">Sita</field>
<field id="SLastNm">M</field>
<field id="RollNumber">002</field>
</student>
<collection id="StudentCollection">
<record>
<field id="StudentId">5094e0ef-c966-484d-9892-e62bd828e7cf</field>
<field id="FirstName">Ram</field>
<field id="LastName">Raju</field>
</record>
<record>
<field id="StudentId">70a0350a-9556-46f6-b089-bebcc278b1c1</field>
<field id="FirstName">Sita</field>
<field id="LastName">M</field>
</record>
</collection>
<collection id="AccountTransactions">
<record>
<field id="Id">62f4181a-5510-4522-a24a-7d3005f2a907</field>
<field id="TransactionType">Fees</field>
<field id="Balance">5000.00</field>
</record>
<record>
<field id="Id">0958d991-c777-46b3-954c-3682ff735bfc</field>
<field id="TransactionType">Fine</field>
<field id="Balance">2000.00</field>
</record>
<record>
<field id="Id">3aadb37d-d066-491f-8525-2e299ad8d88b</field>
<field id="TransactionType">Fees</field>
<field id="Balance">6000.00</field>
</record>
</collection>
<collection id="StudentAccountTransactions">
<record>
<field id="Id">5e7d0c97-8759-4beb-a688-009e23f10590</field>
<field id="StudentId">5094e0ef-c966-484d-9892-e62bd828e7cf</field>
<field id="AccountTransactionsId">62f4181a-5510-4522-a24a-7d3005f2a907</field>
</record>
<record>
<field id="Id">b22bf993-4dc2-49e6-879f-504ca4ec8424</field>
<field id="StudentId">5094e0ef-c966-484d-9892-e62bd828e7cf</field>
<field id="AccountTransactionsId">0958d991-c777-46b3-954c-3682ff735bfc</field>
</record>
<record>
<field id="Id">44641d91-38bf-4e24-895c-20f92f390acf</field>
<field id="StudentId">70a0350a-9556-46f6-b089-bebcc278b1c1</field>
<field id="AccountTransactionsId">3aadb37d-d066-491f-8525-2e299ad8d88b</field>
</record>
</collection>
</students>
</stdXmlFormat>
The students node is the parent node which consists of child nodes as and collections (StudentCollection, AccountTransactions and StudentAccountTransactions).
<stdXmlFormat version="1.0">
<students>
<student></student>
<collection id="StudentCollection"/>
<collection id="AccountTransactions"/>
<collection id="StudentAccountTransactions"/>
</students>
</stdXmlFormat>
students node is containing all the student details such first name,last name, roll number etc(this was an old collection with more information.some attributes i have removed for better reading and understanding)
And same information is available in the new collection StudentCollection but with less data.this collection is having StudentId (a unique guid used for identifying the student in other collections). the only relation with students and StudentCollection is the student first name and last name.
The AccountTransactions is the collection which holds id (a unique guid for identifying each transactions) and other transaction details. The id in this collection used as AccountTransactionsId in the StudentAccountTransactions collection.
The final collection StudentAccountTransactions which holds the studentid (from StudentCollection) and AccountTransactionsId (from AccountTransactions collection).
So from this list i need to get the details of the students and pass it to DB TEAM for saving to database.They are ready with table structure as shown below
Basically i need to fetch the SFirstNm,SLastNm,RollNumber( from student), StudentId,AccountTransactionsId and StudentAccountTransactionsId from this collection.
I have tried to get the data using XMLdocument and Xdocument but nothing worked
string xpath = @"/stdXmlFormat/students/students/field";
XmlNodeList nodeListstudent = root.SelectNodes(xpath);
foreach (XmlNode item in nodeListstudent )
{
string SID = item.Attributes["id"].Value;
string sFirstNm, sLastNm = string.Empty;
foreach (XmlNode childNodes in item .ChildNodes)
{
string NodeName = childNodes.Attributes["id"].Value;
string NodeValue = childNodes.InnerText;
if (NodeName == "sFirstNm")
{
sFirstNm= NodeValue;
}
if (NodeName == "aBLastNm")
{
sLastNm = NodeValue;
}
}
}
From this i was able to get the student first name and last names etc but how i can fetch the student id from StudentCollection using first name and last name? i am really stuck here please help. with that studentid i need to get other details such as AccountTransactionsId and StudentAccountTransactionsId.
I tried to filter the studentid collections using following way for testing purpose but not worked.
XmlNodeList nodes = jsonXmlDocument.SelectNodes("//stdXmlFormat/students/collection/record/field[@id='5094e0ef-c966-484d-9892-e62bd828e7cf']");
Please help me !
CodePudding user response：
Here is a start :
using System;
using System.Linq;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Xml;
using System.Xml.Linq;
namespace ConsoleApp2
{
class Program
{
const string FILENAME = @"c:\temp\test.xml";
static void Main(string[] args)
{
DataTable dt = new DataTable();
dt.Columns.Add("StudentId", typeof(string));
dt.Columns.Add("SFirstNm", typeof(string));
dt.Columns.Add("SLastNm", typeof(string));
dt.Columns.Add("RollNumber", typeof(string));
dt.Columns.Add("AccountTransaction", typeof(string));
dt.Columns.Add("StudentAccountTransaction", typeof(string));
XDocument doc = XDocument.Load(FILENAME);
XElement xStudents = doc.Descendants("students").FirstOrDefault();
var students = xStudents.Elements("student")
.Select(x => new
{
id = (string)x.Attribute("id"),
sFName = (string)x.Elements("field").Where(y => (string)y.Attribute("id") == "SFirstNm").FirstOrDefault(),
sLName = (string)x.Elements("field").Where(y => (string)y.Attribute("id") == "SLastNm").FirstOrDefault(),
rollNumber = (string)x.Elements("field").Where(y => (string)y.Attribute("id") == "RollNumber").FirstOrDefault(),
}).ToList();
XElement xStudentCollection = doc.Descendants("collection").Where(x => (string)x.Attribute("id") == "StudentCollection").FirstOrDefault();
var studentCollection = xStudentCollection.Elements("record")
.Select(x => new
{
sId = (string)x.Elements("field").Where(y => (string)y.Attribute("id") == "StudentId").FirstOrDefault(),
sFName = (string)x.Elements("field").Where(y => (string)y.Attribute("id") == "FirstName").FirstOrDefault(),
sLName = (string)x.Elements("field").Where(y => (string)y.Attribute("id") == "LastName").FirstOrDefault(),
}).ToList();
XElement xAccountTransactions = doc.Descendants("collection").Where(x => (string)x.Attribute("id") == "AccountTransactions").FirstOrDefault();
var accountTransactions = xAccountTransactions.Elements("record")
.Select(x => new
{
tId = (string)x.Elements("field").Where(y => (string)y.Attribute("id") == "Id").FirstOrDefault(),
type = (string)x.Elements("field").Where(y => (string)y.Attribute("id") == "TransactionType").FirstOrDefault(),
balanse = (decimal)x.Elements("field").Where(y => (string)y.Attribute("id") == "Balance").FirstOrDefault(),
}).ToList();
XElement xStudentAccountTransactions = doc.Descendants("collection").Where(x => (string)x.Attribute("id") == "StudentAccountTransactions").FirstOrDefault();
var studentAccountTransactions = xStudentAccountTransactions.Elements("record")
.Select(x => new
{
satId = (string)x.Elements("field").Where(y => (string)y.Attribute("id") == "Id").FirstOrDefault(),
sId = (string)x.Elements("field").Where(y => (string)y.Attribute("id") == "StudentId").FirstOrDefault(),
tId = (string)x.Elements("field").Where(y => (string)y.Attribute("id") == "AccountTransactionsId").FirstOrDefault(),
}).ToList();
var studentRoll = students.Select(s => studentCollection.Where(sc => (s.sFName == sc.sFName) && (s.sLName == sc.sLName))
.Select(x => new { fName = s.sFName, lName = s.sLName, roll = s.rollNumber, sId = x.sId }).FirstOrDefault()).ToList();
var tranactions = accountTransactions.Select(at => studentAccountTransactions.Where(act => at.tId == act.tId)
.Select(x => new { tId = x.tId, type = at.type, balance = at.balanse, satId = x.satId }).FirstOrDefault()).ToList();
var results = (from sat in studentAccountTransactions
join tr in tranactions on sat.tId equals tr.tId
join sr in studentRoll on sat.sId equals sr.sId
select new { sr = sr, tr = tr, sat = sat })
.ToList();
foreach(var r in results)
{
dt.Rows.Add(new object[]
{
r.sr.sId,
r.sr.fName,
r.sr.lName,
r.sr.roll,
r.tr.tId,
r.sat.satId
});
}
}
}
}