Merging two XML variables - please help!

Question

I am using SQL Server Management Studio 17.9.1 with SQL Server 2016 Service Pack 2 (SP2).

There is no table structure to show, just two XML variables that contain XML, as shown below:

DECLARE @A XML = '
<ManageByCountry>
  <CountriesAllowed>
    <Country name="United States" countrycode="US" />
    <Country name="Canada" countrycode="CA" />
  </CountriesAllowed>
</ManageByCountry>
',
@B XML = '
<ManageByCountry>
  <CountriesAllowed>
    <Country name="United Kingdom" countrycode="GB" />
    <Country name="Puerto Rico" countrycode="PR" />
    <Country name="France" countrycode="FR" />
  </CountriesAllowed>
</ManageByCountry>
';

I am looking for a statement that would output this:

<ManageByCountry>
  <CountriesAllowed>
    <Country name="United States" countrycode="US" />
    <Country name="Canada" countrycode="CA" />
    <Country name="United Kingdom" countrycode="GB" />
    <Country name="Puerto Rico" countrycode="PR" />
    <Country name="France" countrycode="FR" />
  </CountriesAllowed>
</ManageByCountry>

My knowledge of querying XML isn't that good and I have no idea where to search first on how I could achieve this.

Answer 1

Use query() to get the Country nodes from both variables and use for xml path to rebuild the XML.

select @A.query('/ManageByCountry/CountriesAllowed/Country'),
       @B.query('/ManageByCountry/CountriesAllowed/Country')
for xml path('CountriesAllowed'), root('ManageByCountry');