Sql-server – How to create XML file by SQL query

sql serverxml

I have to create an XML File using sql

 <externalCodes>
  <externalCode>
   <system>CALYPSO</system>
   <extCode>cptname</extCode>
  </externalCode>
 <externalCode>
   <system>CMS-GDP</system>
   <extCode>cptname</extCode>
 </externalCode>
 <externalCode>
   <system>Manual</system>
   <extCode>cptname</extCode>
 </externalCode>

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:

    <externalCodes>
      <externalCode>
        <system>CALYPSO</system>
        <extCode>Mon</extCode>
        <system>CMS-GDP</system>
        <extCode>Mon</extCode>
        <system>MANUAL</system>
        <extCode>Mon</extCode>
      </externalCode>
     </externalCodes>

But the expected output is as below

   <externalCodes>
     <externalCode>
       <system>CALYPSO</system>
       <extCode>Mon</extCode>
     </externalCode>
    <externalCode>
       <system>CMS-GDP</system>
       <extCode>Mon</extCode>
     </externalCode>
     <externalCode>
       <system>MANUAL</system>
       <extCode>Mon</extCode>
     </externalCode>
    </externalCodes>

Best Answer

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:

<externalCodes>
   <externalCode>
      <system>CALYPSO</system>
      <extCode>Mon</extCode>
   </externalCode>
   <externalCode>
      <system>CMS-GDP</system>
      <extCode>Mon</extCode>
   </externalCode>
   <externalCode>
      <system>MANUAL</system>
      <extCode>Mon</extCode>
   </externalCode>
</externalCodes>