从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>

几点说明：

TYPE : 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.

PATH： 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>