使用嵌套类导出 xml 文件

SQL Server 具有强大的 XML 功能。

Решение только в T-SQL. Одна команда T-SQL ... Очень просто.

Проверьте это в SQL Server Management Studio (SSMS).

SQL

-- DDL и пример данных, начало
DECLARE @doctor TABLE (
    doctor_id INT PRIMARY KEY, 
    surname VARCHAR(20),
    firstname VARCHAR(20),
    patronymic VARCHAR(20),
    profession VARCHAR(20),
    category INT
);
INSERT INTO @doctor
(
    doctor_id,
    surname,
    firstname,
    patronymic,
    profession,
    category
)
VALUES
(1, 'Никитина', 'Нина', 'Петровна', 'Терапевт', 1),
(2, 'Романовна', 'Светлана', 'Николаевна', 'Дерматолог', 6);

DECLARE @patient TABLE (
    patient_id INT PRIMARY KEY, 
    doctor_id INT, 
    surname VARCHAR(20),
    firstname VARCHAR(20),
    patronymic VARCHAR(20),
    date_birth DATE,
    category VARCHAR(20)
);
INSERT INTO @patient
(
    patient_id,
    doctor_id,
    surname,
    firstname,
    patronymic,
    date_birth,
    category
)
VALUES
(1, 1, 'Понамарев', 'Олег', 'Игнатьевич', '1997-02-12', 'Инвалид'),
(2, 1, 'Николаев', 'Георгий', 'Николаевич', '1985-11-17', NULL);

DECLARE @note TABLE (
    note_id INT PRIMARY KEY, 
    patient_id INT, 
    doctor_id INT,
    date_note DATE,
    diagnos VARCHAR(20),
    price DECIMAL(6,2)
);
INSERT INTO @note
(
    note_id,
    patient_id,
    doctor_id,
    date_note,
    diagnos,
    price
)
VALUES
(1, 1, 1, '2020-01-14', 'ОРВИ', 560.00);
-- DDL и пример данных, конец


SELECT *
, (
SELECT *
, (
    SELECT * 
    FROM @note AS n
    WHERE n.patient_id = p.patient_id
    FOR XML PATH('NOTE'), TYPE, ROOT('NOTES')
)
    FROM @patient AS p
    WHERE p.doctor_id = d.doctor_id
    FOR XML PATH('PATIENT'), TYPE, ROOT('PATIENTS')
)
FROM @doctor AS d
FOR XML PATH('DOCTOR'), TYPE, ROOT('DOCTORS');

如果您更正了 XML 中的错误，那么它应该是这样的

<?xml version="1.0" encoding="utf-8"?>
<DOCTORS>
   <DOCTOR>
    <surname>Никитина</surname>
    <name>Нина</name>
    <patronymic>Петровна</patronymic>
    <profession>Терапевт</profession>
    <category>1</category>
    <PATIENTS>
        <PATIENT>
        <surname>Понамарев</surname>
        <name>Олег</name>
        <patronymic>Игнатьевич</patronymic>
        <date_birth>12.02.1997</date_birth>
        <category>Инвалид</category>
        <NOTES>
            <NOTE>
                <date_note>01.03.2020</date_note>
                <diagnos>ОРВИ</diagnos>
                <price>560</price>
            </NOTE>
        </NOTES>
    </PATIENT>
    <PATIENT>
        <surname>Николаев</surname>
        <name>Георгий</name>
        <patronymic>Николаевич</patronymic>
        <date_birth>17.11.1985</date_birth>
        <NOTES>
            <NOTE>
                <date_note>02.03.2018</date_note>
                <diagnos>Грипп</diagnos>
                <price>560</price>
            </NOTE>
            <NOTE>
                <date_note>20.02.2020</date_note>
                <diagnos>Грипп</diagnos>
                <price>560</price>
            </NOTE>
        </NOTES>
    </PATIENT>
    </PATIENTS>
</DOCTOR>
<DOCTOR>
    <surname>Романова</surname>
    <name>Светлана</name>
    <patronymic>Николаевна</patronymic>
    <profession>Дерматолог</profession>
    <category>6</category>
    <PATIENTS />
    </DOCTOR>
</DOCTORS>

要读取和写入这样的文件，您需要以下类

public class XmlDoctorsService
{
    private const string _doctors = "DOCTORS";
    private const string _doctor = "DOCTOR";
    private const string _patients = "PATIENTS";
    private const string _patient = "PATIENT";
    private const string _surname = "surname";
    private const string _name = "name";
    private const string _patron = "patronymic";
    private const string _patientBirth = "date_birth";
    private const string _category = "category";
    private const string _prof = "profession";
    private const string _notes = "NOTES";
    private const string _note = "NOTE";
    private const string _noteDate = "date_note";
    private const string _noteDiagnos = "diagnos";
    private const string _notePrice = "price";

    public XmlDoctorsService()
    { }

    /// <summary>
    /// Получение коллекции докторов из файла XML
    /// </summary>
    /// <param name="file">путь к файлу</param>
    /// <returns>коллекция докторов</returns>
    public List<Doctor> GetDoctors(string file)
    {
        if (String.IsNullOrEmpty(file) || !File.Exists(file))
            throw new ArgumentException(nameof(file));

        var doctors = new List<Doctor>();
        try
        {
            var doc = XDocument.Load(file);

            foreach (XElement xdoctor in doc.Root.Elements(_doctor))
            {
                var doctor = GetDoctorEntity(xdoctor);

                if (xdoctor.Element(_patients) == null)
                {
                    doctors.Add(doctor);
                    continue;
                }

                foreach (XElement xpatient in xdoctor.Element(_patients).Elements(_patient))
                {
                    var patient = GetPatientEntity(xpatient);

                    if (xpatient.Element(_notes) == null)
                    {
                        doctor.Patients.Add(patient);
                        continue;
                    }

                    foreach (XElement xnote in xpatient.Element(_notes).Elements(_note))
                    {
                        Note note = GetNoteEntity(xnote);
                        patient.Notes.Add(note);
                    }

                    doctor.Patients.Add(patient);
                }

                doctors.Add(doctor);
            }
        }
        catch (Exception ex)
        {
            Debug.WriteLine(ex.Message);
        }

        return doctors;
    }

    /// <summary>
    /// Получение экземпляра Note из XElement
    /// </summary>
    /// <param name="xnote"></param>
    /// <returns></returns>
    private Note GetNoteEntity(XElement xnote)
    {
        return new Note
        {
            Date = DateTime.Parse(xnote.Element(_noteDate).Value),
            Diagnosis = xnote.Element(_noteDiagnos).Value,
            Price = decimal.Parse(xnote.Element(_notePrice).Value)
        };
    }

    /// <summary>
    /// Получение экземпляра Patient из XElement
    /// </summary>
    /// <param name="xpatient"></param>
    /// <returns></returns>
    private Patient GetPatientEntity(XElement xpatient)
    {
        return new Patient
        {
            Surname = xpatient.Element(_surname).Value,
            Name = xpatient.Element(_name).Value,
            Patronymic = xpatient.Element(_patron).Value,
            Birthdate = DateTime.Parse(xpatient.Element(_patientBirth).Value),
            Category = xpatient.Element(_category)?.Value,
        };
    }

    /// <summary>
    /// Получение экземпляра Doctor из XElement
    /// </summary>
    /// <param name="xdoctor"></param>
    /// <returns></returns>
    private Doctor GetDoctorEntity(XElement xdoctor)
    {
        return new Doctor
        {
            Surname = xdoctor.Element(_surname).Value,
            Name = xdoctor.Element(_name).Value,
            Patronymic = xdoctor.Element(_patron).Value,
            Profession = xdoctor.Element(_prof).Value,
            Category = int.Parse(xdoctor.Element(_category).Value)
        };
    }

    /// <summary>
    /// Сохранение коллекции докторов в XML файл
    /// </summary>
    /// <param name="file">путь к файлу</param>
    /// <param name="doctors">коллекция докторов</param>
    public void SaveDoctors(string file, List<Doctor> doctors)
    {
        if (String.IsNullOrEmpty(file))
            throw new ArgumentException(nameof(file));
        if (doctors == null || doctors.Count == 0)
            throw new ArgumentException(nameof(doctors));

        var xdoctors = new XElement(_doctors);
        foreach (Doctor doctor in doctors)
        {
            var d = GetDoctorXElement(doctor);
            xdoctors.Add(d);
        }

        var xdoc = new XDocument(xdoctors);
        try
        {
            xdoc.Save(file);
        }
        catch (Exception ex)
        {
            Debug.WriteLine(ex.Message);
        }
    }

    /// <summary>
    /// Получение XElement из экземпляра Doctor
    /// </summary>
    /// <param name="doctor"></param>
    /// <returns></returns>
    private XElement GetDoctorXElement(Doctor doctor)
    {
        return new XElement(_doctor,
            new XElement(_surname, doctor.Surname),
            new XElement(_name, doctor.Name),
            new XElement(_patron, doctor.Patronymic),
            new XElement(_prof, doctor.Profession),
            new XElement(_category, doctor.Category),
            GetPatientsXElement(doctor.Patients));
    }

    /// <summary>
    /// Получение XElement из коллекции Patient
    /// </summary>
    /// <param name="patients"></param>
    /// <returns></returns>
    private XElement GetPatientsXElement(List<Patient> patients)
    {
        var xpatients = new XElement(_patients);
        foreach (Patient patient in patients)
        {
            var p = GetPatientXElement(patient);
            xpatients.Add(p);
        }
        return xpatients;
    }

    /// <summary>
    /// Получение XElement из экземпляра Patient
    /// </summary>
    /// <param name="patient"></param>
    /// <returns></returns>
    private XElement GetPatientXElement(Patient patient)
    {
        return new XElement(_patient,
            new XElement(_surname, patient.Surname),
            new XElement(_name, patient.Name),
            new XElement(_patron, patient.Patronymic),
            new XElement(_patientBirth, patient.Birthdate.ToShortDateString()),
            new XElement(_category, patient.Category),
            GetNotesXElement(patient.Notes));
    }

    /// <summary>
    /// Получение XElement из коллекции Note
    /// </summary>
    /// <param name="notes"></param>
    /// <returns></returns>
    private object GetNotesXElement(List<Note> notes)
    {
        var xnotes = new XElement(_notes);
        foreach (Note note in notes)
        {
            var n = GetNoteXElement(note);
            xnotes.Add(n);
        }
        return xnotes;
    }

    /// <summary>
    /// Получение XElement из экземпляра Note
    /// </summary>
    /// <param name="note"></param>
    /// <returns></returns>
    private XElement GetNoteXElement(Note note)
    {
        return new XElement(_note,
            new XElement(_noteDate, note.Date.ToShortDateString()),
            new XElement(_noteDiagnos, note.Diagnosis),
            new XElement(_notePrice, note.Price.ToString("N")));
    }
}

你没有提供你的数据库模式，我创建了一个这样的

然后，要获得具有专利和诊断的医生的集合，您需要这个类

public class DbDoctorsService
{
    private const string conStr = @"Data Source=(localdb)\MSSQLLocalDB;" +
            "Initial Catalog=DoctorsDB;Integrated Security=True;" +
            "Connect Timeout=60;Encrypt=False;TrustServerCertificate=False;" +
            "ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

    /// <summary>
    /// Получение коллекции Докторов с Пациентами и Диагнозами
    /// </summary>
    /// <returns>коллекция Докторов</returns>
    public List<Doctor> GetDoctors()
    {
        var doctors = new List<Doctor>();

        try
        {
            using (var conn = new SqlConnection(conStr))
            {
                List<Doctor> docs = GetDoctorsWithoutPatients(conn);
                foreach (Doctor doctor in docs)
                {
                    List<Patient> patients = GetPatientsByDoctorId(doctor.Id, conn);
                    if (patients.Count > 0)
                    {
                        foreach (Patient patient in patients)
                        {
                            List<Note> notes = GetNotesByPatientId(patient.Id, conn);
                            if (notes.Count > 0)
                            {
                                patient.Notes = notes;
                            }
                        }
                        doctor.Patients = patients;
                    }

                    doctors.Add(doctor);
                }
            }
        }
        catch (Exception ex)
        {
            Debug.WriteLine(ex.Message);
        }

        return doctors;
    }

    /// <summary>
    /// Получение полного списка Докторов
    /// </summary>
    /// <param name="conn"></param>
    /// <returns></returns>
    private List<Doctor> GetDoctorsWithoutPatients(SqlConnection conn)
    {
        var result = new List<Doctor>();

        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = @"SELECT 
                                Id,
                                Surname,
                                Name,
                                Patronymic,
                                Profession,
                                Category
                                FROM dbo.Doctors;";
            conn.Open();
            using (var reader = cmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        var d = new Doctor
                        {
                            Id = reader.GetInt32(0),
                            Surname = reader.GetString(1),
                            Name = reader.GetString(2),
                            Patronymic = reader.GetString(3),
                            Profession = reader.GetString(4),
                            Category = Convert.ToInt32(reader.GetValue(5))
                        };
                        result.Add(d);
                    }
                }
            }
        }

        return result;
    }

    /// <summary>
    /// Получение списка Пациентов по Id Доктора
    /// </summary>
    /// <param name="doctorId"></param>
    /// <param name="conn"></param>
    /// <returns></returns>
    private List<Patient> GetPatientsByDoctorId(int doctorId, SqlConnection conn)
    {
        var result = new List<Patient>();

        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = @"SELECT 
                                    dbo.Patients.Id,
                                    dbo.Patients.Surname,
                                    dbo.Patients.Name,
                                    dbo.Patients.Patronymic,
                                    dbo.Patients.Birthdate,
                                    dbo.Patients.Category
                                FROM dbo.Doctors
                                INNER JOIN dbo.DoctorNotes
                                    ON dbo.DoctorNotes.DoctorId = dbo.Doctors.Id
                                INNER JOIN dbo.Patients
                                    ON dbo.Patients.Id = dbo.DoctorNotes.PatientId
                                WHERE dbo.Doctors.Id = @id;";

            var param = new SqlParameter();
            param.ParameterName = "id";
            param.SqlDbType = System.Data.SqlDbType.Int;
            param.Value = doctorId;
            cmd.Parameters.Add(param);

            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            using (var reader = cmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        var d = new Patient
                        {
                            Id = reader.GetInt32(0),
                            Surname = reader.GetString(1),
                            Name = reader.GetString(2),
                            Patronymic = reader.GetString(3),
                            Birthdate = reader.GetDateTime(4),
                            Category = Convert.ToString(reader.GetValue(5))
                        };
                        result.Add(d);
                    }
                }
            }
        }

        return result;
    }

    /// <summary>
    /// Получение списка Диагнозов по Id Пациента
    /// </summary>
    /// <param name="patientId"></param>
    /// <param name="conn"></param>
    /// <returns></returns>
    private List<Note> GetNotesByPatientId(int patientId, SqlConnection conn)
    {
        var result = new List<Note>();

        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = @"SELECT 
                                    dbo.Noties.Date,
                                    dbo.Noties.Diagnosis,
                                    dbo.Noties.Price
                                FROM dbo.Patients
                                INNER JOIN dbo.DoctorNotes
                                    ON dbo.DoctorNotes.PatientId = dbo.Patients.Id
                                INNER JOIN dbo.Noties
                                    ON dbo.Noties.Id = dbo.DoctorNotes.NoteId
                                WHERE dbo.Patients.Id = @id;";

            var param = new SqlParameter();
            param.ParameterName = "id";
            param.SqlDbType = System.Data.SqlDbType.Int;
            param.Value = patientId;
            cmd.Parameters.Add(param);

            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            using (var reader = cmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        var d = new Note
                        {
                            Date = reader.GetDateTime(0),
                            Diagnosis = reader.GetString(1),
                            Price = reader.GetDecimal(2)
                        };
                        result.Add(d);
                    }
                }
            }
        }

        return result;
    }
}

您可以像这样使用这些类

class Program
{
    static void Main(string[] args)
    {
        Console.WriteLine("==App10==");

        const string file = @"D:\docs.xml";
        const string fileNew = @"D:\docsNew.xml";
        var xmlDoctors = new XmlDoctorsService();
        var dbDoctors = new DbDoctorsService();

        List<Doctor> doctors = dbDoctors.GetDoctors();

        //List<Doctor> docs = xmlDoctors.GetDoctors(file);
        xmlDoctors.SaveDoctors(fileNew, doctors);

        Console.ReadLine();
    }
}

完整的项目在这里