0

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.

3
  • Do you think mentioning your DBMS and showing the corresponding table structure might be helpful?
    – mustaccio
    Nov 12 '21 at 13:21
  • @mustaccio, you are correct, thank you for pointing this out. I will do so in the future.
    – UdarTenjin
    Nov 12 '21 at 15:06
  • You should do so today (by editing your question) -- it will help future readers to find this question and the useful answer by keywords.
    – mustaccio
    Nov 12 '21 at 15:36
3

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');
1
  • Thank you Mikael for this solution, this is EXACTLY what I was looking for. I need to learn more about syntax relating to XML.
    – UdarTenjin
    Nov 12 '21 at 15:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.