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 4select 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 rawFOR XML EXPLICIT,root('tree') -- demo sql 5declare @xml xmldeclare @schemaVersion varchar(10)='5.1'   set @xml=(select ch.ChannelName as ChannelName,   (select a.AccountName as AccountName,   (select c.CampaignName as CampaignName from Campaign cwhere c.AccountId = A.AccountIdFOR XML PATH('Campaign'),TYPE)  from Account awhere a.ChannelId = ch.ChannelIdand a.AccountId <> 0FOR XML PATH('Account'),TYPE)from Channel chwhere ch.ChannelId <> 0order by ChannelNameFOR 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-4444else @phone end

参考：

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

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