--樹形數據生成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 完整版函數使用

--鄒建 2004.08(引用請保留此信息)--*/
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 文檔需要的必要標志
	采用格式化處理方法,生成的數據帶有縮進表示

--鄒建 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 '<?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','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                                            
----------------------------------------------
<?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>
--*/