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

Question

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 1

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

Answer 2

Here is XQuery FLWOR expression based solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, [location] VARCHAR(20), team CHAR(3), score INT);
INSERT INTO @tbl (location, team, score)
VALUES
('Adelong'     ,'SFP',  104)
,('Adelong'    ,'LIB',  189)
,('Adelong'    ,'CDP',  9  )
,('Hurstville' ,'SFP',  14 )
,('Hurstville' ,'LIB',  64 )
,('Hurstville' ,'CDP',  13 );
-- DDL and sample data population, end

SELECT (
    SELECT *
    FROM @tbl
    FOR XML PATH('r'), TYPE, ROOT('root')).query('<root>
{
   for $x in distinct-values(/root/r/location)
   return <location name="{$x}">
   {
        for $y in /root/r[location/text() = $x]
        return <node>
            {$y/team}
            {$y/score}
        </node> 
    }
    </location>
}
</root>');