我需要构建以下XML数据结构。我已经得到了所需的XML结构，但缺少student标签。

<users>
    <user>
        <id>123</id>
        <name>abc</name>
        <student>
            <student_classes>
                <student_class>
                    <class_name>xyx</class_name>
                </student_class>
            </student_classes>
        </student>
    </user>
</users>

我写了以下SQL查询：

SELECT
    id,
    name,
    (SELECT class_name
     FROM classes a
     WHERE a.id = b.id
     FOR XML PATH('student_class'), TYPE) AS student classes
FROM 
    student b
FOR XML RAW('USER'), ELEMENTS, ROOT('USERS')

我已经得到了所需的XML结构，但仍然缺少student标签。该如何添加？

解决方案

请尝试以下解决方案。

你只需要在XML层级再增加一个层级。

XML是大小写敏感的。你的T-SQL元素名称与期望的输出不匹配。

例如：PATH('user') <> PATH('USER')

在XML组合中，最好使用 FOR XML PATH() 子句。

SQL

-- DDL and sample data population, start
CREATE TABLE students (
    id   INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE classes (
    class_id   INT IDENTITY PRIMARY KEY,
    student_id INT NOT NULL,
    class_name VARCHAR(100) NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

INSERT INTO students (id, name)
VALUES (123, 'abc'),
       (200, 'john');   -- extra student to show multi-row behavior

INSERT INTO classes (student_id, class_name)
VALUES (123, 'xyx'),
       (123, 'math'),
       (200, 'history');
-- DDL and sample data population, end

SELECT
    u.id,
    u.name,
    (
        SELECT
            (
                SELECT
                    c.class_name
                FROM classes AS c
                WHERE c.student_id = u.id
                FOR XML PATH('student_class'), TYPE
            )
        FOR XML PATH('student_classes'), TYPE
    ) AS student
FROM students AS u
FOR XML PATH('user'), TYPE, ROOT('users');

输出

<users>
  <user>
    <id>123</id>
    <name>abc</name>
    <student>
      <student_classes>
        <student_class>
          <class_name>xyx</class_name>
        </student_class>
        <student_class>
          <class_name>math</class_name>
        </student_class>
      </student_classes>
    </student>
  </user>
  <user>
    <id>200</id>
    <name>john</name>
    <student>
      <student_classes>
        <student_class>
          <class_name>history</class_name>
        </student_class>
      </student_classes>
    </student>
  </user>
</users>

备选方案

在这种情况下，你可以同时使用一个 ROOT 参数以及一个列别名。

SELECT
    id,
    name,
    (SELECT class_name
     FROM classes c
     WHERE c.student_id = s.id
     FOR XML PATH('student_class'), ROOT('student_classes'), TYPE) AS student
FROM 
    student s
FOR XML PATH('user'), ROOT('users');

另一种替代方案是使用复合名称，如果你使用 PATH 而不是 RAW，会更灵活，并且允许将XQuery路径作为别名。

     FOR XML PATH('student_class'), TYPE) AS [student/student_classes]

db<>fiddle