如何为SQL 'FOR XML'生成的HTML标签添加class类名及自定义内嵌结构

解决方案 #

你只需修改自定义函数和外层查询的XML字段映射规则即可，调整后的代码如下：

1. 修改自定义函数

CREATE FUNCTION dbo.fn_menuXML( @IDMenuParent INT )
RETURNS XML
WITH RETURNS NULL ON NULL INPUT 
BEGIN RETURN 
(
SELECT 
    NavigateUrl AS "a/@href", 
    'collapse' AS "a/@class",
    -- 如果要使用表中存储的ImageUrl，把下方固定路径替换为 ImageUrl 字段即可
    'images/dashboard-normal.png' AS "a/i/img/@src",
    DisplayText AS "a/span",
    CASE WHEN EXISTS(SELECT 1 FROM SFM_Menu WHERE MenuParentID = MenuID AND IsActive=1)
        THEN dbo.fn_menuXML( MenuID )
    END
    FROM SFM_Menu
    WHERE MenuParentID = @IDMenuParent
        AND IsActive=1
    ORDER BY DisplayOrder
    FOR XML PATH('li'), ROOT('ul'), TYPE
)
END
GO

2. 修改外层查询语句

SELECT 
    NavigateUrl AS "a/@href", 
    'collapse' AS "a/@class",
    -- 同上，可替换为 ImageUrl 字段使用表中存储的图片路径
    'images/dashboard-normal.png' AS "a/i/img/@src",
    DisplayText AS "a/span",
    dbo.fn_menuXML(MenuID)
FROM SFM_Menu
WHERE MenuParentID =0 
    AND IsActive=1
ORDER BY DisplayOrder
FOR XML PATH('li'), ROOT('ul'), TYPE

核心改动说明

• 新增'collapse' AS "a/@class"给a标签添加指定class属性
• 新增'images/dashboard-normal.png' AS "a/i/img/@src"生成包裹在i标签内的img节点
• 将原来直接映射到a节点的DisplayText改为DisplayText AS "a/span"，让菜单文本包裹在span标签内
• 函数内新增IsActive过滤条件，避免生成已禁用的子菜单，同时调整子菜单判断逻辑，仅存在有效子菜单时才递归生成ul节点

运行上述代码即可生成你要求的HTML结构。

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