snippetsqlMinor

How to create XML file by SQL query?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

filecreatesqlqueryxmlhow

Problem

I have to create an XML File using sql


  
   CALYPSO
   cptname
  
 
   CMS-GDP
   cptname
 
 
   Manual
   cptname

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:


      
        CALYPSO
        Mon
        CMS-GDP
        Mon
        MANUAL
        Mon

But the expected output is as below


     
       CALYPSO
       Mon
     
    
       CMS-GDP
       Mon
     
     
       MANUAL
       Mon

Solution

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:


   
      CALYPSO
      Mon
   
   
      CMS-GDP
      Mon
   
   
      MANUAL
      Mon

Code Snippets

;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')

<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>

Context

StackExchange Database Administrators Q#191022, answer score: 4

Revisions (0)

No revisions yet.