128.XMl菜单

短命女 2024-02-19 19:08 18阅读 0赞

--生成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                                                          
    -------------------------------------------------------------
    
    
        
            
            
        
        
            
            
        
    
    --*/