--树形数据生成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'TF'))
drop function [dbo].[f_xmlend_mark]
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)+'</ITEM>'
+char(13)+char(10)
return(@r)
end
go
/*--生成树形数据xml标志
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),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],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 '<?xml version="1.0" encoding="gb2312"?>'
union all select '<ROOT>'
insert @re(id,level,xml_L,xml_r)
select a.id,a.level
,space(a.level*4)+'<ITEM'
,case when b.id is null then '>' else '/>' end
from @t a left join(
select id=[id] from [tb] aa
where not exists(
select 1 from [tb] where [pid]=aa.[id])
)b on a.id=b.id
order by a.sid
select @sid=id,@l=level from @re where sid=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
insert @re(xml_L) select '</ROOT>'
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','上海'
union all select '06','江苏'
union all select '07','06','苏州'
union all select '08','07','常熟'
union all select '09','南京'
union all select '10','无锡'
union all select '11','02','纽约'
union all select '12','旧金山'
go
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
----------------------------------------------
<?xml version="1.0" encoding="gb2312"?>
<ROOT>
<ITEM id="01" name="中国">
<ITEM id="04" name="北京"/>
<ITEM id="05" name="上海"/>
<ITEM id="06" name="江苏">
<ITEM id="07" name="苏州">
<ITEM id="08" name="常熟"/>
</ITEM>
<ITEM id="09" name="南京"/>
<ITEM id="10" name="无锡"/>
</ITEM>
</ITEM>
<ITEM id="02" name="美国">
<ITEM id="11" name="纽约"/>
<ITEM id="12" name="旧金山"/>
</ITEM>
<ITEM id="03" name="加拿大"/>
</ROOT>
--*/
