FOR XML in SQL Server: Elements based on common values of columns

I’m using FOR XML to create an XML file out of SQL queries. I want to have elements based around common values for a particular column, in this case “location”:

location    team    score
Adelong     SFP     104
Adelong     LIB     189
Adelong     CDP     9
Hurstville  SFP     14
Hurstville  LIB     64
Hurstville  CDP     13

I can easily convert his into XML row-by-row like so:

SELECT(SELECT location, team, score FROM MyTable FOR XML PATH('node'), TYPE ) FOR XML PATH(''), ROOT('root')

Which produces XML that looks liks this:

  <node>
    <location>Adelong</location>
    <team>SFP</team>
    <score>104</score>
  </node>
  <node>
    <location>Adelong</location>
    <team>LIB</team>
    <score>189</score>
  </node>
  <node>
    <location>Adelong</location>
    <team>CDP</team>
    <score>9</score>
  </node>

But what I actually want is this:

<location name="Adelong">
    <node>
        <team>SFP</team>
        <score>104</score>
    </node>
    <node>
        <team>LIB</team>
        <score>189</score>
    </node>
    <node>
        <team>CDP</team>
        <score>9</score>
    </node>
</location>

Answer

You can use an ad hoc derived table that gets the distinct locations, select the locations from it and get the teams and scores in a correlated subquery.

SELECT t1.location [@name],
       (SELECT t2.team,
               t2.score
               FROM mytable t2
               WHERE t2.location = t1.location
               FOR XML PATH('node'),
                       TYPE)
       FROM (SELECT DISTINCT
                    t1.location
                    FROM mytable t1) t1
       FOR XML PATH('location');

db<>fiddle