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

​