How to create XML file by SQL query? I have to create an XML File using sql

 &lt;externalCodes&gt;
  &lt;externalCode&gt;
   &lt;system&gt;CALYPSO&lt;/system&gt;
   &lt;extCode&gt;cptname&lt;/extCode&gt;
  &lt;/externalCode&gt;
 &lt;externalCode&gt;
   &lt;system&gt;CMS-GDP&lt;/system&gt;
   &lt;extCode&gt;cptname&lt;/extCode&gt;
 &lt;/externalCode&gt;
 &lt;externalCode&gt;
   &lt;system&gt;Manual&lt;/system&gt;
   &lt;extCode&gt;cptname&lt;/extCode&gt;
 &lt;/externalCode&gt;




My SQL is as follows

(SELECT system, extCode from(Select 'CALYPSO' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 'extCode') a
FOR XML PATH(''), TYPE, ELEMENTS) AS "externalCodes/externalCode",

(SELECT system, extCode from(Select 'CMS-GDP' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 'extCode') b
FOR XML PATH(''), TYPE, ELEMENTS) AS "externalCodes/externalCode",

(SELECT system, extCode from( Select 'MANUAL' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 'extCode') c
FOR XML PATH(''), TYPE, ELEMENTS) AS "externalCodes/externalCode"


My output comes like below:

    &lt;externalCodes&gt;
      &lt;externalCode&gt;
        &lt;system&gt;CALYPSO&lt;/system&gt;
        &lt;extCode&gt;Mon&lt;/extCode&gt;
        &lt;system&gt;CMS-GDP&lt;/system&gt;
        &lt;extCode&gt;Mon&lt;/extCode&gt;
        &lt;system&gt;MANUAL&lt;/system&gt;
        &lt;extCode&gt;Mon&lt;/extCode&gt;
      &lt;/externalCode&gt;
     &lt;/externalCodes&gt;


But the expected output is as below

   &lt;externalCodes&gt;
     &lt;externalCode&gt;
       &lt;system&gt;CALYPSO&lt;/system&gt;
       &lt;extCode&gt;Mon&lt;/extCode&gt;
     &lt;/externalCode&gt;
    &lt;externalCode&gt;
       &lt;system&gt;CMS-GDP&lt;/system&gt;
       &lt;extCode&gt;Mon&lt;/extCode&gt;
     &lt;/externalCode&gt;
     &lt;externalCode&gt;
       &lt;system&gt;MANUAL&lt;/system&gt;
       &lt;extCode&gt;Mon&lt;/extCode&gt;
     &lt;/externalCode&gt;
    &lt;/externalCodes&gt;
I don't have your data source, so I created a cte with some records in it

;with cte
AS 
(
       Select 'CALYPSO' AS 'system', 'Mon' as cptname
       union all Select 'CMS-GDP' , 'Mon'
       union all Select 'MANUAL' , 'Mon'
)
SELECT system, cptname as extCode
from 
   cte
FOR XML PATH('externalCode'), TYPE, ELEMENTS ,ROOT('externalCodes')


output for it:

&lt;externalCodes&gt;
   &lt;externalCode&gt;
      &lt;system&gt;CALYPSO&lt;/system&gt;
      &lt;extCode&gt;Mon&lt;/extCode&gt;
   &lt;/externalCode&gt;
   &lt;externalCode&gt;
      &lt;system&gt;CMS-GDP&lt;/system&gt;
      &lt;extCode&gt;Mon&lt;/extCode&gt;
   &lt;/externalCode&gt;
   &lt;externalCode&gt;
      &lt;system&gt;MANUAL&lt;/system&gt;
      &lt;extCode&gt;Mon&lt;/extCode&gt;
   &lt;/externalCode&gt;
&lt;/externalCodes&gt;