How to output XML from a regular SQL query?

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>

• Answer:

  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