127.树形数据生成XML

--树形数据生成XML
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_xml_LR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_xml_LR]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_xmlend_mark]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_xmlend_mark]
GO

/*--补充生成树形数据 xml 尾部标志

	配合生成 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

/*--生成树形数据 xml 标志

	生成树形数据生成 xml 文档需要的必要标志
	采用格式化处理方法,生成的数据带有缩进表示

--邹建 2004.08(引用请保留此信息)--*/

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 [id],@l,id
	from [tb]
	where [pid]=0
	while @@rowcount>0
	begin
		set @l=@l+1
		insert @t select a.[id],@l,b.sid+','+a.[id]
		from [tb] a,@t b
		where a.[pid]=b.id and b.level=@l-1
	end

	insert @re(xml_L)
	select ''
	union all select ''

	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

	insert @re(xml_L) select ''

	return
end
go

--示例数据
create table [tb](id varchar(10),pid varchar(10),name varchar(20))
insert [tb] select '01',''  ,'中国'
union all select '02',''  ,'美国'
union all select '03',''  ,'加拿大'
union all select '04','01','北京'
union all select '05','01','上海'
union all select '06','01','江苏'
union all select '07','06','苏州'
union all select '08','07','常熟'
union all select '09','06','南京'
union all select '10','06','无锡'
union all select '11','02','纽约'
union all select '12','02','旧金山'
go

--调用生成xml树
select  re=case 
	when a.[id] is null 
	then b.xml_L
	else b.xml_L+' id="'+a.[id]+'" name="'+a.[name]+'"'	+b.xml_R
	end
from [tb] a 
	right join f_xml_LR() b on a.[id]=b.id
order by b.sid
go

--删除测试
drop table [tb]
--drop function f_xmlend_mark,f_xml_LR

/*--测试结果

re                                            
----------------------------------------------


    
        
        
        
            
                
            
            
            
        
    
    
        
        
    
    

--*/