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

要得到

Channel    

         account         

               campaign

这样的树型Xml结构

表结构为：

注意：这里有多个 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
select TOP 10
    1 as Tag,
    null as parent,
    ChannelID as [row!1!ChannelID],
    ChannelName as [row!1!ChannelName],
    Description as [row!1!Description!xml]
from Channel 
--for xml raw
FOR XML EXPLICIT,root('tree')



 -- demo sql 5
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))
  

 


 

生成内容为：

其它：

将字符串分割显示：

declare @phone varchar(20)='1112224444'
select case when len(@phone)=10 
			then substring(@phone,1,3) + '-' + substring(@phone,4,3) + '-' + substring(@phone,7,4) -- 结果：111-222-4444
	else @phone end

参考：

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

http://www.cnblogs.com/youring2/archive/2008/11/27/1342288.html