xml菜单.sql

sqlserver 数据库编程的绝好脚本

--生成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)+'</TREENODE>'
				+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)+'<TREENODE'
		,case when b.MenuID is null then '>' else '/>' end
	from @t a left join(
		select MenuID from Menu aa 
		where not exists(
			select 1 from Menu where FMenuID=aa.MenuID)
	)b on a.id=b.MenuID
	order by a.sid
	select @sid=@@rowcount,@l=level from @re where id=scope_identity()

	update a set xml_L=dbo.f_xmlend_mark(b.level,a.level)+a.xml_L
	from @re a,@re b 
	where a.level<b.level
		and a.sid=b.sid+1
	
	if @l>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 '<?xml version="1.0" encoding="gb2312"?>'
union all select '<TREENODES>'

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 '</TREENODES>'

select * from #t
drop table #t
go

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

/*--测试结果

xml                                                          
-------------------------------------------------------------
<?xml version="1.0" encoding="gb2312"?>
<TREENODES>
    <TREENODE TEXT="文件" NAVIGATEURL="文件(&File)">
        <TREENODE TEXT="打开" NAVIGATEURL="打开(&Open)"/>
        <TREENODE TEXT="退出" NAVIGATEURL="退出(&Exit)"/>
    </TREENODE>
    <TREENODE TEXT="工具" NAVIGATEURL="工具(&Tools)">
        <TREENODE TEXT="查找" NAVIGATEURL="查找(&Find)"/>
        <TREENODE TEXT="替换" NAVIGATEURL="替换(&Replace)"/>
    </TREENODE>
</TREENODES>
--*/