Início / dba / Perguntas / 191022
Accepted
V_immo
Asked: 2017-11-16 13:19:25 +0800 CST

Como criar arquivo XML por consulta SQL?

  • 772

Eu tenho que criar um arquivo XML usando 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>

Meu SQL é o seguinte

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

Minha saída vem como abaixo:

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

Mas a saída esperada é como abaixo

   <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>
sql-server xml

1 respostas

  1. Best Answer

    Não tenho sua fonte de dados, então criei um cte com alguns registros nele

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

    saída para ele:

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

relate perguntas