[SQL] 產出巢狀式 XML

2011-08-11

4217
0
Code Note
2011-12-26

摘要:[SQL] 產出巢狀式 XML

　　又是一個工作需要 , 順便學習使用新方法來達到需求

1. 先來建立一張資料表


USE [TestDB]
GO

/****** Object:  Table [dbo].[Employees]    Script Date: 08/11/2011 17:11:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Employees](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[FullName] [nvarchar](120) NULL,
	[EmployeeNo] [nvarchar](1000) NULL,
	[EmployeeIdUp] [int] NULL,
	[Layer] [int] NULL,
 CONSTRAINT [PK_Organization] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

2. 再新增幾筆資料


insert into Employees values(N'總經理室',N'總經理室','M0001',null,0)

insert into Employees values(N'特助室',N'總經理室/特助室','C0001',1,1)

insert into Employees values(N'經理室',N'總經理室/經理室','M0101',1,1)

insert into Employees values(N'會計室',N'總經理室/會計室','M0102',1,1)

insert into Employees values(N'大特助',N'總經理室/特助室','C00011',2,2)

insert into Employees values(N'小特助',N'總經理室/特助室','C00012',2,2)

insert into Employees values(N'專案經理A',N'總經理室/經理室','M01011',3,2)

insert into Employees values(N'專案經理B',N'總經理室/經理室','M00012',3,2)

insert into Employees values(N'專案經理C',N'總經理室/經理室','M00013',3,2)

3. 利用 SQL FOR XML 來產生巢狀式 XML


select Id as '@EmployeeId',ISNULL(FullName,'') as '@FullName',Name as '@Name',EmployeeIdUp as '@EmployeeIdUp',
( select Id as '@EmployeeId',ISNULL(FullName,'') as '@FullName',Name as '@Name',EmployeeIdUp as '@EmployeeIdUp',
( select Id as '@EmployeeId',ISNULL(FullName,'') as '@FullName',Name as '@Name',EmployeeIdUp as '@EmployeeIdUp' from Employees as Level2 
	where Layer=2 and Level1.Id=Level2.EmployeeIdUp order by Level2.Id for xml Path('Organization'), type) from Employees as Level1 
			where Layer=1 and Level0.Id=Level1.EmployeeIdUp order by Level1.Id for xml Path('Organization'), type) from Employees as Level0 where Level0.Id=1
	 for xml Path('Organization'), type

4. 產生出來的結果如下圖

　5. 程式的部分，再用 ExecuteXMLReader 來接收即可

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝　以上是在網路很常見的範例　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

　　相信很多人會把第 3 點的語法改 CTE 來執行，拔特，在 CTE 裡是不能執行 FOR XML，雖然可以在最後把 CTE 抛出來的資料表使用 FOR XML ，

跑來的 XML 是有巢狀式啦，不過會跟你想像中的不太一樣，在下為了這一點也傷腦筋很久（其實是腦筋不太靈活），最後想出來一個可能不妥當的方式

，利用動態組成查詢字串的方式（嘿嘿嘿.........偷偷參考 Hunterpo前輩及johnny前輩）組合出語法，如下所示：


declare @depth int
 declare @childe varchar(10)
 declare @parent varchar(10)
 declare @outputStatement nvarchar(4000)
 set @depth=10 --層數
 while(@depth>0)
 begin
	set @childe='Level'+CAST(@depth as varchar(2))
	set @parent='Level'+CAST(@depth-1 as varchar(2))
	if(@outputStatement is null)
	begin
	
		set @outputStatement='select Id as ''@EmployeeId'',ISNULL(FullName,'''') as ''@FullName'',Name as ''@Name'',EmployeeIdUp as ''@EmployeeIdUp'' from Employees as '+@childe+' 
			where Layer='+CAST(@depth as varchar(2))+' and '+@parent+'.Id='+@childe+'.EmployeeIdUp order by '+@childe+'.Id for xml Path(''Employees''), type'
	end
	else
	begin
		set @outputStatement='select Id as ''@EmployeeId'',ISNULL(FullName,'''') as ''@FullName'',Name as ''@Name'',EmployeeIdUp as ''@EmployeeIdUp'',( '
			+@outputStatement+') from Employees as '+@childe+' 
			where Layer='+CAST(@depth as varchar(2))+' and '+@parent+'.Id='+@childe+'.EmployeeIdUp order by '+@childe+'.Id for xml Path(''Employees''), type'
	end
   set @depth=@depth-1
 end
 
 set @outputStatement='select Id as ''@EmployeeId'',ISNULL(FullName,'''') as ''@FullName'',Name as ''@Name'',EmployeeIdUp as ''@EmployeeIdUp'',( '
	+@outputStatement+') from Employees as '+@parent+' 
	where '+@parent+'.Layer=0 for xml Path(''Employees''), type'

print @outputStatement
EXEC sp_executesql @outputStatement

使用的方法可能不是很好，畢竟用了迴圈來跑，如果有更好的方式或能改進的地方，希望各位前輩多多請教

===================

大家好 , 我叫芋宅宅

我很菜 , 請各位前輩指教

回首頁