SQL Export using XML explicit, generate elements from a sub/different query

Question

I have an account table and a campaign table, each account has certain number of campaigns associated with it. No I want to export the account IDs and all the Campaign IDs associated with that account id in to XML in this structure

<Accounts>
            <Account>
                        <AccountID></AccountID>
                        <AccountName></AccountName>
                        <CampaignIDs>
                                    <CampaignID></CampaignID>
                                    <CampaignID></CampaignID>
                        </CampaignIDs>
            </Account>
</Accounts>

I am using XML Explicit to control the output of the data into XML and here is what I have got so far.

SELECT
    1 AS Tag,
    NULL AS Parent,
    NULL AS 'Accounts!1',
    NULL AS 'Account!2!AccountID!Element',
    NULL AS 'Account!2!AccountName!Element',
    NULL AS 'Account!2!FMID!Element'
    UNION ALL
 SELECT
 2 AS Tag,
 1 AS Parent, 
 NULL,
 a.id as AccountID,
 a.Name as AccountName,
 NULL
 from Account a
FOR XML EXPLICIT

Now I want to execute another query like Select id from campaign where accountid = var and then append all those campaign IDs to the xml structure.

How do I go about this?

Answer 1

I would recommend using FOR XML PATH instead of FOR XML EXPLICIT - it's much easier to use and much more expressive.

See this:

-- set up test data
declare @Accounts table (AccountID INT, AccountName VARCHAR(50))
declare @Campaigns table (CampaignID INT, AccountID INT, CampaignName varchar(50))

insert into @Accounts values(1, 'Account #1'),(2, 'Account #2')
insert into @Campaigns values(1, 1, 'Campaign #1-1'), (2, 1, 'Campaign #2-1'), (3, 1, 'Campaign #3-1'),
(4, 2, 'Campaign #1-2'), (5, 2, 'Campaign #2-2')

-- SELECT with FOR XML PATH and a nested SELECT/FOR XML PATH,TYPE    
select 
    AccountID,
    AccountName,
    (SELECT CampaignID  
     FROM @Campaigns c
     WHERE c.AccountID = a.AccountID
     FOR XML PATH(''),TYPE) AS 'CampaignIDs'
FROM 
    @Accounts a
FOR XML PATH('Account'),ROOT('Accounts')

This SELECT statement gives me the following output:

<Accounts>
  <Account>
    <AccountID>1</AccountID>
    <AccountName>Account #1</AccountName>
    <CampaignIDs>
      <CampaignID>1</CampaignID>
      <CampaignID>2</CampaignID>
      <CampaignID>3</CampaignID>
    </CampaignIDs>
  </Account>
  <Account>
    <AccountID>2</AccountID>
    <AccountName>Account #2</AccountName>
    <CampaignIDs>
      <CampaignID>4</CampaignID>
      <CampaignID>5</CampaignID>
    </CampaignIDs>
  </Account>
</Accounts>