Query to get XML output for hierarchical data using FOR XML PATH in SQL Server
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>
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
This query will do it - however, it's not very clean in that you have to "manually" define the nesting and it won't just automatically scale to more levels deep....
SELECT
n.ID AS '@Id',
n.NAME AS '@Name',
(SELECT
n2.ID AS '@Id',
n2.NAME AS '@Name',
(SELECT
n3.ID AS '@Id',
n3.NAME AS '@Name'
FROM
dbo.Nodes n3
WHERE
n3.ParentNode = n2.ID
FOR XML PATH('Node'), TYPE
)
FROM
dbo.Nodes n2
WHERE
n2.ParentNode = n.ID
FOR XML PATH('Node'), TYPE
)
FROM
dbo.Nodes n
WHERE
n.ParentNode IS NULL
FOR XML PATH('Node'), ROOT('Nodes')
Output is:
<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>
I was hoping there would be a way to do this with a recursive CTE (Common Table Expression), but that didn't work out :-(
精彩评论