使用SQL Server生成指定结构的圣经XML文件

需求概述 #

需要从两张数据表（Books和Verses）生成符合以下结构的XML文件，用于演示软件的多译本圣经内容：

<Bible>
  <book name="Gênesis" abbrev="gn" nchapters="50" >
    <chapter n="1">
       <verse n="1"> No princípio criou Deus o céu e a terra.</verse>
       <verse n="2"> A terra era sem forma e vazia; [...] a face das águas.</verse>
       <verse n="3"> Disse Deus: haja luz. E houve luz.</verse>
    </chapter>
    <chapter n="2">
      [...] 
    </chapter>
  </book>
  <book name="Êxodo" abbrev="êx" nchapters="40" >
    <chapter n="1">
       <verse n="1"> Estes pois são os nomes dos filhos...</verse>
       [...]
    </chapter>
    [...]
  </book>
  [...]
</Bible>

数据表结构 #

Books表

Verses表

解决方案 #

根据使用的数据库类型，选择对应的SQL查询生成目标XML：

1. MySQL 版本 #

通过字符串拼接和GROUP_CONCAT构造XML，需先调整参数避免内容截断：

-- 调整group_concat最大长度（按需设置）
SET SESSION group_concat_max_len = 1000000;

SELECT CONCAT('<Bible>', GROUP_CONCAT(book_xml SEPARATOR ''), '</Bible>') AS bible_xml
FROM (
    SELECT CONCAT(
        '<book name="', REPLACE(b.name, '"', '&quot;'), '" abbrev="', LOWER(b.abbrev), '" nchapters="', b.nchapters, '" >',
        GROUP_CONCAT(chapter_xml SEPARATOR ''),
        '</book>'
    ) AS book_xml
    FROM Books b
    LEFT JOIN (
        SELECT 
            book,
            CONCAT(
                '<chapter n="', chapter, '">',
                GROUP_CONCAT(
                    CONCAT('<verse n="', verse, '">', REPLACE(REPLACE(text, '<', '&lt;'), '>', '&gt;'), '</verse>')
                    ORDER BY verse ASC
                    SEPARATOR ''
                ),
                '</chapter>'
            ) AS chapter_xml
        FROM Verses
        GROUP BY book, chapter
    ) v ON b.name = v.book
    GROUP BY b.Book_id, b.name, b.abbrev, b.nchapters
) AS books_xml;

2. SQL Server 版本 #

利用原生FOR XML PATH语法生成嵌套XML结构：

SELECT
    b.name AS '@name',
    LOWER(b.abbrev) AS '@abbrev',
    b.nchapters AS '@nchapters',
    (
        SELECT
            v.chapter AS '@n',
            (
                SELECT
                    verse AS '@n',
                    text AS '*'
                FROM Verses v2
                WHERE v2.book = b.name AND v2.chapter = v.chapter
                ORDER BY verse
                FOR XML PATH('verse'), TYPE
            )
        FROM Verses v
        WHERE v.book = b.name
        GROUP BY v.chapter
        ORDER BY v.chapter
        FOR XML PATH('chapter'), TYPE
    )
FROM Books b
ORDER BY b.Book_id
FOR XML PATH('book'), ROOT('Bible');

3. PostgreSQL 版本 #

使用XMLELEMENT和XMLAGG函数构建嵌套XML：

SELECT XMLELEMENT(
    NAME "Bible",
    XMLAGG(
        XMLELEMENT(
            NAME "book",
            XMLATTRIBUTES(
                b.name AS "name",
                LOWER(b.abbrev) AS "abbrev",
                b.nchapters AS "nchapters"
            ),
            (
                SELECT XMLAGG(
                    XMLELEMENT(
                        NAME "chapter",
                        XMLATTRIBUTES(v.chapter AS "n"),
                        (
                            SELECT XMLAGG(
                                XMLELEMENT(
                                    NAME "verse",
                                    XMLATTRIBUTES(v2.verse AS "n"),
                                    v2.text
                                )
                                FROM Verses v2
                                WHERE v2.book = b.name AND v2.chapter = v.chapter
                                ORDER BY v2.verse
                            )
                        )
                    )
                    FROM Verses v
                    WHERE v.book = b.name
                    GROUP BY v.chapter
                    ORDER BY v.chapter
                )
            )
        )
        ORDER BY b.Book_id
    )
) AS bible_xml
FROM Books b;

注意事项 #

• 所有方案均处理了XML特殊字符（双引号、尖括号）的转义，避免格式错误。
• 确保Verses表的book字段与Books表的name字段完全匹配，否则会出现内容缺失。
• 针对大体积内容，需调整数据库配置（如MySQL的group_concat_max_len）避免截断。

内容的提问来源于stack exchange，提问作者Kawe