128.XMl菜单

--生成xml菜单

--示例数据
IF EXISTS(SELECT name  FROM sysobjects WHERE  name = 'Menu' AND type = 'U')
    DROP TABLE Menu
Create table Menu(MenuID tinyint primary key identity,MenuName varchar(20),CommandURL varchar(20),FMenuID tinyint)
insert Menu select '文件','文件(&File)'   ,0
union  all  select '工具','工具(&Tools)'  ,0
union  all  select '打开','打开(&Open)'   ,1
union  all  select '退出','退出(&Exit)'   ,1
union  all  select '查找','查找(&Find)'   ,2
union  all  select '替换','替换(&Replace)',2
go

--处理 xml 尾部标志的函数
create function f_xmlend_mark(
@p_level int,	--上条记录的层次
@level int		--本条记录的层次
)returns nvarchar(800)
as
begin
	declare @r nvarchar(800)
	set @r=''
	while @p_level>@level
		select @p_level=@p_level-1
			,@r=@r+space(@p_level*4)+''
				+char(13)+char(10)
	return(@r)
end
go

create function f_xml_LR()
returns @re table(sid int identity,id varchar(10),level int,xml_L Nvarchar(1000),xml_R Nvarchar(1000))
as
begin
	--生成排序后的编号列表
	declare @t table(id varchar(10),level int,sid varchar(8000))

	declare @l int,@sid int
	set @l=1
	insert @t select MenuID,@l,right(1000+MenuID,3)
	from Menu
	where FMenuID=0
	while @@rowcount>0
	begin
		set @l=@l+1
		insert @t select a.MenuID,@l,b.sid+','+right(1000+a.MenuID,3)
		from Menu a,@t b
		where a.FMenuID=b.id and b.level=@l-1
	end

	insert @re(id,level,xml_L,xml_r)
	select a.id,a.level
		,space(a.level*4)+'1
		update @re set xml_R=xml_R+char(13)+char(10)+dbo.f_xmlend_mark(@l,1)
		where sid=@sid
	return
end
go

--调用生成xml树
create table #t([xml] nvarchar(4000))
insert #t select ''
union all select ''

insert #t
select  re=b.xml_L
	+' TEXT="'+MenuName+'" NAVIGATEURL="'+CommandURL+'"'
	+b.xml_R
from Menu a,f_xml_LR() b
where a.MenuID=b.id
order by b.sid

insert #t select ''

select * from #t
drop table #t
go

--删除测试
drop table Menu
drop function f_xmlend_mark,f_xml_LR

/*--测试结果

xml                                                          
-------------------------------------------------------------


    
        
        
    
    
        
        
    

--*/