要得到

Channel    

         account         

               campaign

表结构为：

注意：这里有4个 demo的sql语句，注意区分

---- demo sql 1
--select ch.ChannelName as "@Text",
--       (select a.AccountName as "@Text",
--               (select c.CampaignName as "@Text" 
--                from Campaign c
--                where c.AccountId = A.AccountId
--                FOR XML PATH(‘Campaign‘), TYPE
--                )  
--        from Account a
--        where a.ChannelId = ch.ChannelId
--        and a.AccountId <> 0
--        FOR XML PATH(‘Account‘), TYPE
--        )
--from Channel ch
--where ch.ChannelId <> 0
--order by ChannelName
--FOR XML PATH(‘Channel‘), ROOT(‘Tree‘)



---- demo sql 2
--select ch.ChannelName as ChannelName,
--       (select a.AccountName as AccountName,
--               (select c.CampaignName as CampaignName 
--                from Campaign c
--                where c.AccountId = A.AccountId
--                FOR XML PATH(‘Campaign‘),TYPE
--                )  
--        from Account a
--        where a.ChannelId = ch.ChannelId
--        and a.AccountId <> 0
--        FOR XML PATH(‘Account‘),TYPE
--        )
--from Channel ch
--where ch.ChannelId <> 0
--order by ChannelName
--FOR XML PATH(‘Channel‘), ROOT(‘Tree‘)



---- demo sql 3
--;WITH XMLNAMESPACES (DEFAULT ‘http://www.reuters.com/Datascope/ReportRequest.xsd‘)
--select ch.ChannelName as ChannelName,
--       (select a.AccountName as AccountName,
--               (select c.CampaignName as CampaignName 
--                from Campaign c
--                where c.AccountId = A.AccountId
--                FOR XML PATH(‘Campaign‘),TYPE
--                )  
--        from Account a
--        where a.ChannelId = ch.ChannelId
--        and a.AccountId <> 0
--        FOR XML PATH(‘Account‘),TYPE
--        )
--from Channel ch
--where ch.ChannelId <> 0
--order by ChannelName
--FOR XML PATH(‘Channel‘), ROOT(‘Tree‘)




-- demo sql 4
declare @xml xml
declare @schemaVersion varchar(10)=‘5.1‘  
 
set @xml=
(
	select ch.ChannelName as ChannelName,
		   (select a.AccountName as AccountName,
				   (select c.CampaignName as CampaignName 
					from Campaign c
					where c.AccountId = A.AccountId
					FOR XML PATH(‘Campaign‘),TYPE
					)  
			from Account a
			where a.ChannelId = ch.ChannelId
			and a.AccountId <> 0
			FOR XML PATH(‘Account‘),TYPE
			)
	from Channel ch
	where ch.ChannelId <> 0
	order by ChannelName
	FOR XML PATH(‘Channel‘), ROOT(‘Tree‘),ELEMENTS XSINIL
)

set @xml.modify(‘insert attribute schemaVersion{sql:variable(‘‘@schemaVersion‘‘)} as last into (/Tree)[1]‘)   --向根节点添加schemaVersion 属性
select @xml

--SELECT CAST( 
--CAST ((‘<?xml version="1.0" encoding="iso8859-1"?>‘+ cast(@xml  varchar(max)) AS VARBINARY (MAX)) 
-- AS XML)
 --SELECT ‘<?xml version="1.0" encoding="iso8859-1"?>‘ + cast(@xml as varchar(max))
  

 


 

生成内容为：

参考：

http://blog.csdn.net/iwteih/article/details/2607177

sql for xml: 生成树型结构的xml文件 （sql for xml to tree ）,布布扣,bubuko.com

sql for xml: 生成树型结构的xml文件 （sql for xml to tree ）