Return tree-type Xml from SQLServer

The database table structure is as follows:

To get

Channel

Account

Campaign

For such a tree-like Xml structure, the SQL statement is as follows:

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 chwhere ch.ChannelId <> 0order by ChannelNameFOR XML PATH('Channel'), ROOT('Tree')

 

The output result is as follows:

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

 

Notes:
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 ()").
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 ).

If data () is used for multiple data sets, a record with multiple datasets separated by spaces is output. For details, see OrderID below.

There is an example on msdn, with an example as follows (connected to the Northwind database ):

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 CustomersFOR XML PATH('Customer')

 

Output:

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