如何通过SQL查询创建XML文件？

Question

我必须使用sql创建一个XML文件

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

我的SQL如下所示

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

我的输出如下：

    <externalCodes>
      <externalCode>
        <system>CALYPSO</system>
        <extCode>Mon</extCode>
        <system>CMS-GDP</system>
        <extCode>Mon</extCode>
        <system>MANUAL</system>
        <extCode>Mon</extCode>
      </externalCode>
     </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>

Answer 1

我没有您的数据源，所以我创建了一个包含一些记录的cte

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