如何将类名或样式名添加到使用 SQL 'FOR XML'生成的 HTML 标记

Question

我有下面的查询，以分层方式选择菜单并显示在asp.net网页，

在输出中我得到<li><a href="#">Products</a></li>，我需要将输出转换为如下

<li><a href="#" class="collapse"><i><img src="images/dashboard-normal.png"></i><span>Products</span></a></li>

即，我需要包括类名，需要包括图像标签内的li，需要添加span标签(在我目前的输出没有类名，图像标签)

谁能帮我实现这个?

CREATE FUNCTION dbo.fn_menuXML( @IDMenuParent INT )
RETURNS XML
WITH RETURNS NULL ON NULL INPUT 
BEGIN RETURN 
(
SELECT 
NavigateUrl AS "a/@href", 
DisplayText AS "a", 
CASE WHEN MenuParentID = @IDMenuParent
THEN dbo.fn_menuXML( MenuID )
END
FROM SFM_Menu
WHERE MenuParentID = @IDMenuParent
FOR XML PATH('li'), ROOT('ul'), TYPE
)
END
GO
SELECT 
NavigateUrl AS "a/@href", 
DisplayText AS "a", 
dbo.fn_menuXML(MenuID)

FROM SFM_Menu
WHERE MenuParentID =0 
AND IsActive=1
ORDER BY DisplayOrder
FOR XML PATH('li'), ROOT('ul'), TYPE

请查看下面的模式和数据，

DECLARE @Menu TABLE
(
MenuID INT,
MenuParentID INT,
DisplayText NVARCHAR(100),
NavigateUrl NVARCHAR(255),
ImageUrl NVARCHAR(255),
IsActive int,
DisplayOrder bit
);
INSERT INTO @Menu VALUES (1, 0, 'Products', 'Products.aspx','Sample.png',1,1) ,
(2, 1, 'Add Products', 'AddProducts.aspx','Sample.png',1,1),
(3, 1, 'Update Products', 'UpdateProducts.aspx','Sample.png',1,2),
(4, 1, 'Product Type', 'ProductType.aspx','Sample.png',1,3),
(5, 4, 'Logistics', 'Logistics.aspx','Sample.png',1,1),
(9, 0, 'Department', '#','Sample.png',1,2),
(10, 9, 'Clothing', 'Clothing.aspx','Sample.png',1,1),
(11, 9, 'Dresses', 'Dresses.aspx','Sample.png',1,1),
(12, 9, 'Accessories', 'Accessories.aspx','Sample.png',1,3),
(13, 0, 'Site Management', '#','Sample.png',1,3),
(14, 13, 'Security', 'Security.aspx','Sample.png',1,1) ,
(6, 4, 'Food Items', 'FoodItems.aspx','Sample.png',1,1),
(7, 6, 'Veg', 'Veg.aspx','Sample.png',1,1) ,
(8, 6, 'Non Veg', 'NonVeg.aspx','Sample.png',1,2) ;

输出应该像这样，

<ul>
<li>
<a href="Products.aspx" class="collapse">
<i>
<img src="images/dashboard-normal.png" />
</i>
<span>Products</span>
</a>
<ul>
<li>
<a href="AddProducts.aspx" class="collapse">
<i>
<img src="images/dashboard-normal.png" />
</i>
<span>Add Products</span>
</a>
</li>
<li>
<a href="UpdateProducts.aspx" class="collapse">
<i>
<img src="images/dashboard-normal.png" />
</i>
<span>Update Products</span>
</a>
</li>
<li>
<a href="ProductType.aspx" class="collapse">
<i>
<img src="images/dashboard-normal.png" />
</i>
<span>Product Type</span>
</a>
<ul>
<li>
<a href="Logistics.aspx" class="collapse">
<i>
<img src="images/dashboard-normal.png" />
</i>
<span>Logistics</span>
</a>
</li>
<li>
<a href="FoodItems.aspx" class="collapse">
<i>
<img src="images/dashboard-normal.png" />
</i>
<span>Food Items</span>
</a>
<ul>
<li>
<a href="Veg.aspx" class="collapse">
<i>
<img src="images/dashboard-normal.png" />
</i>
<span>Veg</span>
</a>
</li>
<li>
<a href="NonVeg.aspx" class="collapse">
<i>
<img src="images/dashboard-normal.png" />
</i>
<span>Non Veg</span>
</a>
</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
<li>
<a href="#" class="collapse">
<i>
<img src="images/dashboard-normal.png" />
</i>
<span>Department</span>
</a>
<ul>
<li>
<a href="Clothing.aspx" class="collapse">
<i>
<img src="images/dashboard-normal.png" />
</i>
<span>Clothing</span>
</a>
</li>
<li>
<a href="Dresses.aspx" class="collapse">
<i>
<img src="images/dashboard-normal.png" />
</i>
<span>Dresses</span>
</a>
</li>
<li>
<a href="Accessories.aspx" class="collapse">
<i>
<img src="images/dashboard-normal.png" />
</i>
<span>Accessories</span>
</a>
</li>
</ul>
</li>
<li>
<a href="#" class="collapse">
<i>
<img src="images/dashboard-normal.png" />
</i>
<span>Site Management</span>
</a>
<ul>
<li>
<a href="Security.aspx" class="collapse">
<i>
<img src="images/dashboard-normal.png" />
</i>
<span>Security</span>
</a>
</li>
</ul>
</li>
</ul>

Answer 1

请尝试以下解决方案。

使用递归CTE来基于指定的父节点构造菜单。

不清楚从哪里获取图片链接。

/p>

-- DDL and sample data population, start
DECLARE @Menu TABLE
(
MenuID INT,
MenuParentID INT,
DisplayText NVARCHAR(100),
NavigateUrl NVARCHAR(255),
IsActive int,
DisplayOrder bit
);
INSERT INTO @Menu VALUES 
(1, 0, 'Products', 'Products.aspx',1,1) ,
(2, 1, 'Add Products', 'AddProducts.aspx',1,1),
(3, 1, 'Update Products', 'UpdateProducts.aspx',1,2),
(4, 1, 'Product Type', 'ProductType.aspx',1,3),
(5, 4, 'Logistics', 'Logistics.aspx',1,1),
(9, 0, 'Department', '#',1,2),
(10, 9, 'Clothing', 'Clothing.aspx',1,1),
(11, 9, 'Dresses', 'Dresses.aspx',1,1),
(12, 9, 'Accessories', 'Accessories.aspx',1,3),
(13, 0, 'Site Management', '#',1,3),
(14, 13, 'Security', 'Security.aspx',1,1) ,
(6, 4, 'Food Items', 'FoodItems.aspx',1,1),
(7, 6, 'Veg', 'Veg.aspx',1,1),
(8, 6, 'Non Veg', 'NonVeg.aspx',1,2);
-- DDL and sample data population, end
DECLARE @IDMenuParent INT = 0;
-- Find all child nodes of the specified parent node
-- just to see
;WITH Hierarchy AS
(
SELECT Path=CAST(T1.DisplayText + '/' AS VARCHAR(1000)), Level=1, T1.MenuID,T1.MenuParentID,T1.DisplayText, T1.NavigateUrl 
FROM @Menu T1
WHERE T1.MenuParentID = @IDMenuParent
UNION ALL
SELECT Path=CAST(TH.Path + T1.DisplayText + '/' AS VARCHAR(1000)),Level=TH.Level+1, T1.MenuID,T1.MenuParentID,T1.DisplayText, T1.NavigateUrl
FROM @Menu T1 INNER JOIN 
Hierarchy TH ON TH.MenuID = T1.MenuParentID
)
SELECT * FROM Hierarchy
ORDER BY MenuID; 
-- real deal
;WITH Hierarchy AS
(
SELECT Path=CAST(T1.DisplayText + '/' AS VARCHAR(1000)), Level=1, T1.MenuID,T1.MenuParentID,T1.DisplayText, T1.NavigateUrl 
FROM @Menu T1
WHERE T1.MenuParentID = @IDMenuParent
UNION ALL
SELECT Path=CAST(TH.Path + T1.DisplayText + '/' AS VARCHAR(1000)),Level=TH.Level+1, T1.MenuID,T1.MenuParentID,T1.DisplayText, T1.NavigateUrl
FROM @Menu T1 INNER JOIN 
Hierarchy TH ON TH.MenuID = T1.MenuParentID
)
SELECT (
SELECT * 
FROM Hierarchy
ORDER BY MenuID
FOR XML PATH('li'), TYPE, ROOT('ul')
).query('<ul>
{
for $x in /ul/li
return <li>
<a href="{$x/NavigateUrl}" class="collapse">
<i>
<img src="images/dashboard-normal.png"/>
</i>
<span>{data($x/DisplayText)}</span>
</a>
</li>
}
</ul>');