Nesting XML elements with FOR XML, PATH

Question

I have a table EVENTS:

Name    Event       Gift        Label
Jeff    Birthday    Card        Happy Birthday
Jeff    Birthday    Present     Jeff's prezzy
Maisey  Promotion   Cake        Well Done!
Maisey  Birthday    Card        Happy Birthday
Maisey  Birthday    Cake        Another Year Older!

Here's the code to produce that table:

DECLARE @TempTable TABLE
(
    [name] VARCHAR(250),
    [event] VARCHAR(250),
    [gift] VARCHAR(250),
    [label] VARCHAR(2500)
);

INSERT INTO @TempTable([name], [event], [gift], [label]) VALUES
('Jeff','Birthday','Card','Happy Birthday'),
('Jeff','Birthday','Present','Jeff`s prezzy'),
('Maisey','Promotion','Cake','Well Done!'),
('Maisey','Birthday','Card','Happy Birthday'),
('Maisey','Birthday','Cake','Another Year Older!')

Which I would like to generate the following XML from:

<celebrations>
    <person name="Jeff">
        <events>
            <event name="Birthday">
                <gifts>
                    <gift name="Card" Value="Happy Birthday" />
                    <gift name="Present" Value="Jeff's prezzy" />
                </gifts>
            </event>
        </events>
    </person>
    <person name="Maisey">
        <events>
            <event name="Promotion">
                <gifts>
                    <gift name="Cake" value="Well Done!" />
                </gifts>
            </event>
            <event name="Birthday">
                <gifts>
                    <gift name="Card" value="Happy Birthday" />
                    <gift name="Cake" value="Another Year Older!" />
                </gifts>
            </event>
        </events>
    </person>
</celebrations>

My current SQLXML looks like this:

SELECT (
        SELECT (
                SELECT T1.*
                FOR XML path('')
                    ,root('gifts')
                    ,type
                )
        FROM @TempTable AS T1
        FOR XML path('person')
            ,type
        )
FOR XML path('persons')

Which is currently producing this:

<persons>
  <person>
    <gifts>
      <name>Jeff</name>
      <event>Birthday</event>
      <gift>Card</gift>
      <label>Happy Birthday</label>
    </gifts>
  </person>
  <person>
    <gifts>
      <name>Jeff</name>
      <event>Birthday</event>
      <gift>Present</gift>
      <label>Jeff`s prezzy</label>
    </gifts>
  </person>
  <person>
    <gifts>
      <name>Maisey</name>
      <event>Promotion</event>
      <gift>Cake</gift>
      <label>Well Done!</label>
    </gifts>
  </person>
  <person>
    <gifts>
      <name>Maisey</name>
      <event>Birthday</event>
      <gift>Card</gift>
      <label>Happy Birthday</label>
    </gifts>
  </person>
  <person>
    <gifts>
      <name>Maisey</name>
      <event>Birthday</event>
      <gift>Cake</gift>
      <label>Another Year Older!</label>
    </gifts>
  </person>
</persons>

How do I format it to look like the XML in the portion at the top of this post?

Answer 1

Try this, the magic is the correlated sub-query with GROUP BY:

DECLARE @TempTable TABLE
(
    [name] VARCHAR(250),
    [event] VARCHAR(250),
    [gift] VARCHAR(250),
    [label] VARCHAR(2500)
);

INSERT INTO @TempTable([name], [event], [gift], [label]) VALUES
('Jeff','Birthday','Card','Happy Birthday'),
('Jeff','Birthday','Present','Jeff`s prezzy'),
('Maisey','Promotion','Cake','Well Done!'),
('Maisey','Birthday','Card','Happy Birthday'),
('Maisey','Birthday','Cake','Another Year Older!')

SELECT t1.[Name] AS [@name]
      ,(
        SELECT T2.[Event] AS [@name]
              ,(
                SELECT T3.gift AS [@name], T3.label AS [@value]
                FROM @TempTable T3
                WHERE T1.[Name]=T3.[Name] AND T2.[event] = T3.[event]
                GROUP BY T3.gift, T3.label
                FOR XML PATH('gift'),ROOT('gifts'),TYPE
               )
        FROM @TempTable T2
        WHERE T1.[Name]=T2.[Name]
        GROUP BY T2.[event]
        FOR XML PATH('event'),ROOT('events'),TYPE
       ) 
FROM @TempTable AS T1
GROUP BY t1.[Name]
FOR XML PATH('person'),ROOT('celebrations'),TYPE;

The result

<celebrations>
  <person name="Jeff">
    <events>
      <event name="Birthday">
        <gifts>
          <gift name="Card" value="Happy Birthday" />
          <gift name="Present" value="Jeff`s prezzy" />
        </gifts>
      </event>
    </events>
  </person>
  <person name="Maisey">
    <events>
      <event name="Birthday">
        <gifts>
          <gift name="Cake" value="Another Year Older!" />
          <gift name="Card" value="Happy Birthday" />
        </gifts>
      </event>
      <event name="Promotion">
        <gifts>
          <gift name="Cake" value="Well Done!" />
        </gifts>
      </event>
    </events>
  </person>
</celebrations>