Here is something that I got off the internet for a project that I was working on. The aim was to create a reusable authentication module that could be compiled into a dll. The User administration part of this requires that a multiple level menu is returned. The data is stored as an adjacency list in a SQL table. I used the following to convert the adjacency list into an XML file that can be used by the client application:
--------------------------------------------------------------------
--Function: AS_fn_menu
--Purpose: Returns a menu to the calling app in XML format
--------------------------------------------------------------------
--note that this did not work in the beginning until I included the ",XMLDATA".
--I then ran the query, but SQL generated a schema and made the data unusable.
--I then commented out the ", XMLDATA" again, and this time, the complete XML
--was returned to the query.
alter function AS_fn_menu(@parent varchar(30)) RETURNS XML
as
begin
return
(
select ParentMenu,ItemText,dbo.AS_fn_menu(ItemText)
from ASMenu
WHERE ParentMenu=@parent
FOR XML AUTO --, XMLDATA
)
END
select dbo.AS_fn_menu('Administration')