javascript - 如何在 NodeJS 中从 MYSQL 生成具有多个子元素的 XML

Question

你能帮我做以下事情吗？我有以下XML文件，并且能够将其解析为MYSQL中的两个表。一张表用于根<studentInformation>，第二张用于<course>. 我在课程表上使用外键来建立两者之间的关系。

<listofStudents>
 <studentInformation>
        <firstname>John</firstname>
        <surname>Doe</surname>
        <age>18</age>
        <studentNo>61937JD</studentNo>
        <race>white</race>
        <sex>Male</sex>
        <faculty>Electrical Engineering</faculty>
        <yearOfStudy>1</yearOfStudy>
        <course>
            <courseName>Computer Science</courseName>
            <dateRegistered>01/01/2020</name>
            <currentScore>81%</currentScore>
            <semester>1</semester>
            <status>completed</status>
        </course>
        <course>
            <courseName>Philosophy</courseName>
            <dateRegistered>01/06/2020</name>
            <currentScore>96%</currentScore>
            <semester>2</semester>
            <status>completed</status>
        </course>
        <hobby>Computer Programming</hobby>
    </studentInformation>
    <studentInformation>
        <firstname>Jane</firstname>
        <surname>Dassi</surname>
        <age>19</age>
        <studentNo>62550JD</studentNo>
        <race>White</race>
        <sex>Female</sex>
        <faculty>Biological Sciences</faculty>
        <yearOfStudy>2</yearOfStudy>
        <course>
            <courseName>Introduction to Biology</courseName>
            <dateRegistered>01/01/2021</name>
            <currentScore>49%</currentScore>
            <semester>1</semester>
            <status>failed</status>
        </course>
        <course>
            <courseName>Chemistry</courseName>
            <dateRegistered>01/01/2021</name>
            <currentScore>37%</currentScore>
            <semester>1</semester>
            <status>failed</status>>
        </course>
        <hobby>Netball</hobby>
    </studentInformation>
</listofStudents>

在NodeJs 中，我创建了如下表格：

onnection.query('CREATE DATABASE IF NOT EXISTS students', (err, res) => {
    if (err) throw err;
    connection.query('USE rates_db', (err, res) =>{
        if(err) console.log(err);
       // connection.query('DROP TABLE IF EXISTS marketDataItems_tbl', (err, res) => {
          //  if(err) console.log(err);
            connection.query('CREATE TABLE IF NOT EXISTS marketDataItems_tbl(' +
            'id INT NOT NULL,' +
            'PRIMARY KEY(id),' +
            'firstname VARCHAR(255) NOT NULL,' +
            'surname VARCHAR(255) NOT NULL,' +
            'age VARCHAR(255) NOT NULL,' +
            'studentNo INT NOT NULL,' +
            'race VARCHAR(255) NOT NULL,' +
            'sex VARCHAR(255) NOT NULL,' +
            'faculty VARCHAR(255) NOT NULL,' +
            'yearOfStudy VARCHAR(255) NOT NULL,' +
            'hobby VARCHAR(255) NOT NULL' +
            ')', err => {
                if (err) console.log(err);
            });
        //});
        //connection.query('DROP TABLE IF EXISTS course', (err, res) =>{
            //if (err) throw err;
            connection.query('CREATE TABLE IF NOT EXISTS course('+
            'id INT NOT NULL AUTO_INCREMENT,' +
            'courseName VARCHAR(255) NOT NULL,' +
            'dateRegistered VARCHAR(255) NOT NULL,' +
            'currentScore VARCHAR(255) NOT NULL,' +
            'semester VARCHAR(255) NOT NULL,' +
            'status VARCHAR(255) NOT NULL,' +
            'PRIMARY KEY (id),' +
            'student_id INT NOT NULL,'+
            'FOREIGN KEY (student_id) REFERENCES students(id)'+
            ')',err => {
                if(err) console.log(err);
            });
        //});
    });
});

这目前允许人们打开网页并编辑学生信息和课程信息。因此，我尝试使用新更新的信息重新生成原始 * XML 。请参阅下面的尝试，但它不适用于课程元素，即我可以创建一个包含所有学生信息元素的XML，但是当我尝试添加课程时，它立即不起作用。

var data = {};
let listOfStudents = () => {  
    connection.query('USE school_db', (err, res) =>{    
        if(err) console.log(err);
        connection.query('SELECT * FROM students', (err, res) =>{
            if(err) console.log(err);
            data = JSON.parse(JSON.stringify(res));
            
        })
    });
   return data;
}

let listOfStudentCourses = () => {
connection.query('USE school_db', (err, results) =>{
                if(err) console.log(err)
                connection.query('SELECT * FROM course', (err, result) =>{
                    if(err) console.log(err);
                    //students_data = res
                    console.log(result)
                })
    });
}


let xmlContents = function createXMLFileContents(){
    for(let studentItemIndex = 0; studentItemIndex != studentDataItemslist.length; studentItemIndex++){       
        for(let courseItemIndex = 0; courseItemIndex !=coursesItemslist.length; coursesItemslist++){
               let courseItemobject = coursesItemslist[courseItemIndex];
                    let trimmedCourseItemObject = courseItemobject;
               if(courseItemobject['studentdataitem_id'] == studentItemIndex+1){
                   delete trimmedCourseItemObject.id;
                   delete trimmedCourseItemObject.studentdataitem_id;
                   studentDataItemslist[studentItemIndex]['course'] = [];
                    studentDataItemslist[studentItemIndex]['course'][courseItemIndex] = trimmedCourseItemObject;
               }
        }
    }
    return studentDataItemslist;
}
let jsonXMLContents = JSON.stringify(xmlContents());

问题：如何使用来自 MYSQL 的新更新信息重新创建原始 XML， 即在 MYSQL 中有数据如何查询 MYSQL 的两个具有外部关系的表以创建一个类似于我解析为 xml 的 XML。

标签： javascriptmysqlnode.jsxml