sql - 如何在 sql server 2008 中为多对多关系生成嵌套 XML？

Question

情况是有类(class)和讲师，他们是多对多的关系(1个类(class)可以有多个讲师，1个讲师可以教授多个类(class))

以下 XML 重复类(class)名称:

SELECT
    C.COURSE_ID "@course_id"
    ,   C.COURSE_NAME "Course_Name"
    --get lecturer id(s) of the course
    ,   CL.LECTURER_ID  

    FROM 
    COURSE C LEFT JOIN COURSE_LECTURER CL ON C.COURSE_ID = CL.COURSE_ID
    --LEFT JOIN AS ALL COURSES DON'T HAVE LECTURERS COULD BE INNER :)
FOR XML PATH('Course'), ROOT('Program')

它给了我以下输出(片段):

<Course course_id="ISFND 1.1">
    <Course_Name>Relational Databases and SQL</Course_Name>
    <LECTURER_ID>ME123</LECTURER_ID>
  </Course>
  <Course course_id="ISFND 1.1">
    <Course_Name>Relational Databases and SQL</Course_Name>
    <LECTURER_ID>HS123</LECTURER_ID>
  </Course>

但我想要的输出是:

<Course course_id="ISFND 1.1">
        <Course_Name>Relational Databases and SQL</Course_Name>
        <LECTURER_ID>ME123</LECTURER_ID>
        <LECTURER_ID>HS123</LECTURER_ID>
      </Course>

我想我的 sql 中存在一些问题，可能是我需要为每个使用 IN 或类似的东西。任何帮助将不胜感激。谢谢你。

Answer 1

SELECT
    C.COURSE_ID "@course_id"
    ,   C.COURSE_NAME "Course_Name"
    , (SELECT CL.LECTURER_ID
       FROM COURSE_LECTURER CL
       WHERE C.COURSE_ID = CL.COURSE_ID
       FOR XML PATH(''), TYPE)
FROM COURSE C
FOR XML PATH('Course'), ROOT('Program')