树形数据生成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'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)+'</ITEM>'

+char(13)+char(10)

return(@r)

end

go

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

生成树形数据生成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

--调用生成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

----------------------------------------------

<?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>

--*/

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!