Personally I would use XQuery to do the transformation. I save xslt for the hardest of transformations, but to be honest, I have been able to do most things in XQuery. If the data is already stored in your sql tables, this makes it a bit easier as you can use tsql and not have to worry about creating a clr or another app/ssis to read the data out, do the transformation and save it back.
for example, I quickly created the following based on your example you documented to do the transformation using xquery:
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>
')
You can see the main guts of the code is quite small and remodels the xml as you require.