从SQLServer 返回树型Xml

数据库表结构如下

要得到

Channel    

         account         

               campaign

这样的树型Xml结构， SQL 语句如下：

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')

 

输出结果如下

<Tree>    
  <Channel Text="Astrology">    
    <Account Text="MSN Astrology">    
      <Campaign Text="Astrology" />  
    </Account>  
    <Account Text="MSN Astrology">
      <Campaign Text="Astrology - Chinese" />  
      <Campaign Text="Astrology - General" />  
      <Campaign Text="Astrology - Charts & Reports" />
    </Account>
  </Channel>
  <Channel Text="Autos">
    <Account Text="MSN Auto">
      <Campaign Text="MSN Autos" />
      <Campaign Text="MSN Autos_TSA" />
    </Account>
    <Account Text="MSN Autos">
      <Campaign Text="Certified Pre-Owned/Used" />
      <Campaign Text="General Auto/Car" />
      <Campaign Text="Homepage" />
    </Account>
  </Channel>
</Tree>

 

几点说明:
We can leverage the new TYPE directive to generate XML data type instances
(otherwise, you will get a textual result that will be entitized if it is embedded in another FOR XML query) and nest sub selections to define the hierarchy.

The PATH mode allows you to use an XPath-like syntax as a column name, which   
then is mapped into an attribute (e.g., "@a"), element (e.g.,   
"e"), sub element structure ("e1/e2"), element content   
("*"), text node ("text()"), or data value ("data()"). As   
with the RAW mode, the default name for the row element is row and can be   
overwritten with an NCName (a name without a prefix). 

若data()用于多个数据集时，会输出一个多个数据集以空格为间隔的记录，可参见下面OrderID。

msdn上有一篇例文， 其中有一例子如下（连接的是Northwind数据库）: 

SELECT CustomerID as "@ID",
      (SELECT OrderID as "data()"
       FROM Orders
       WHERE Customers.CustomerID=Orders.CustomerID
       FOR XML PATH('')
      ) as "@OrderIDs",
       CompanyName,
       ContactTitle as "ContactName/@ContactTitle",
       ContactName as "ContactName/text()",
       PostalCode as "Address/@ZIP",
       Address as "Address/Street",
       City as "Address/City"
FROM Customers
FOR XML PATH('Customer')

 

输出为：

<Customer ID="HUNGC" OrderIDs="10375 10394 10415 10600 10660">    
  <CompanyName>Hungry Coyote Import Store</CompanyName>
  <ContactName  
	ContactTitle="Sales Representative">Yoshi Latimer   </ContactName>
    <Address ZIP="97827">    
    <Street>City Center Plaza 516 Main St.</Street>
    <City>Elgin</City>
  </Address>
</Customer>