我有一个包含 NodeId、NodeName、ParentNodeId 列的表,我想使用 SQL 查询以 Xml 的形式输出整个表数据,如下所示.我认为 Sql server 中的 FOR XML PATH 模式可用于使用递归来实现这一点(我使用 SQL Server 2008),但不确定如何.提前致谢
I have a table with columns NodeId, NodeName, ParentNodeId and I want to ouput entire table data in the form of Xml like the following using SQL query. I think FOR XML PATH mode in Sql server can be used to achieve this (I use SQL Server 2008) using recursion, but not sure how. Thanks in advance
<?xml version="1.0" encoding="utf-8" ?>
<Nodes>
<Node Id="1" Name="node1">
<Node Id="11" Name="node11">
<Node Id="111" Name="node111" />
<Node Id="112" Name="node112" />
</Node>
</Node>
<Node Id="2" Name="node2">
<Node Id="21" Name="node21">
<Node Id="211" Name="node211" />
<Node Id="212" Name="node212" />
</Node>
</Node>
</Nodes>
我使用存储过程和递归函数解决了这个问题.代码如下所示.(实际上我希望它生成一个菜单xml,因此显示了菜单的代码.
I solved it using a stored procedure and a recursive function. code shown below. (actually I wanted this to generate a menu xml, so the code is shown for the menu.
CREATE PROCEDURE [dbo].[usp_GetMenu]
AS
BEGIN
SET NOCOUNT ON;
SELECT dbo.fnGetMenuItems(MenuId)
FROM dbo.Menu
WHERE ParentMenuId IS NULL
FOR XML PATH('MenuItems')
END
GO
CREATE FUNCTION [dbo].[fnGetMenuItems]
(
@MenuId int
)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN
(
SELECT MenuId AS "@Id"
, [Name] AS "@Name"
, [URL] AS "@URL"
, [Key] AS "@Key"
, [dbo].[fnGetMenuItems](MenuId)
FROM dbo.Menu
WHERE ParentMenuId = @MenuId
FOR XML PATH('MenuItem'),TYPE
)
END
GO
这篇关于在 SQL Server 中使用 FOR XML PATH 查询以获取分层数据的 XML 输出的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持html5模板网!