DECLARE @xml XML
SET @xml = '<Appointments>
<Appointment id = "1">
<person>
<Name>John Doe</Name>
</person>
<location>
<name>Main</name>
<address>456 Oak St.</address>
<State>NY</State>
</location>
</Appointment>
<Appointment id = "2">
<person>
<Name>Jane Doe</Name>
</person>
<location>
<name>Main</name>
<address>456 Oak St.</address>
<State>NY</State>
</location>
</Appointment>
<Appointment id = "3">
<person>
<Name>george Doe</Name>
</person>
<location>
<name>Satellite</name>
<address>123 Main St.</address>
<State>NY</State>
</location>
</Appointment>
</Appointments>'

SELECT @xml.query('for $x in /Appointments return
<Appointments>
	{
		for $y in distinct-values($x/Appointment/location/name/text())
		return
			($x/Appointment/location[name = $y])[1]
			
	}
	{
		for $y in $x/Appointment
		return
			<Appointment id="{$y/@id}">
				{$y/person}
				<location name="{$y/location/name/text()}"/>
			</Appointment>
	}
</Appointments>
')