--生成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
-------------------------------------------------------------
--*/
128.XMl菜单
最新推荐文章于 2024-06-04 12:45:39 发布