Create a XML from SQL Server

Question

I need some help. I'm trying to create an XML with this query:

WITH XMLNAMESPACES ('Envelope' AS soapenv) 
    SELECT * 
    FROM CrearViaje4
    JOIN transporte ON CrearViaje4.Cod_viaje = transporte.Cod_viaje
    JOIN depositoSalida ON transporte.Cod_viaje = depositoSalida.cod_viaje
    FOR XML AUTO, ROOT('soapenv:Body'), ELEMENTS

and I get this result:

  <soapenv:Body xmlns:soapenv="Envelope">
      <CrearViaje4>
        <cod_viaje>A02VAIFHDXH</cod_viaje>
        <Empresa>Tradelog</Empresa>
        <transporte>
          <cod_viaje>A02VAIFHDXH</cod_viaje>
          <depositoSalida>
            <cod_viaje>A02VAIFHDXH</cod_viaje>
          </depositoSalida>
        </transporte>
      </CrearViaje4>
    </soapenv:Body>

Why is transporte is under the table depositoSalida?

I am trying to get this result, what can I do?

<soapenv:Body xmlns:soapenv="Envelope">
  <CrearViaje4>
    <cod_viaje>A02VAIFHDXH</cod_viaje>
    <Empresa>Tradelog</Empresa>
        <transporte>
           <cod_viaje>A02VAIFHDXH</cod_viaje>
        </transporte>
      <depositoSalida>
        <cod_viaje>A02VAIFHDXH</cod_viaje>
      </depositoSalida>
   </CrearViaje4>
</soapenv:Body>

score 1 · Answer 1 · answered Aug 26 '19 at 21:36

you could try joining depositoSalida on CrearViaje4.Cod_viaje = depositoSalida.cod_viaje

with xmlnamespaces ('Envelope' as soapenv)
select  *
from    CrearViaje4
join    transporte on CrearViaje4.Cod_viaje = transporte.Cod_viaje
join    depositoSalida on CrearViaje4.Cod_viaje = depositoSalida.cod_viaje
for xml auto, root('soapenv:Body'), elements

or using sub queries to get your extra data

with xmlnamespaces ('Envelope' as soapenv)
select  *
        , (   select    *
              from      transporte
              where     CrearViaje4.Cod_viaje = transporte.Cod_viaje
              for xml path(''), type ) as transporte
        , (   select    *
              from      depositoSalida
              where     CrearViaje4.Cod_viaje = depositoSalida.Cod_viaje
              for xml path(''), type ) as depositoSalida
from    CrearViaje4
for xml auto, root('soapenv:Body'), elements

This is a good and comprehensive answer, +1 from my side... Although I would recommend to avoid `AUTO` mode and use `PATH` in (almost) all cases. The one exception is `EXPLICIT`, if you need unsupported hacks with namespaces or directives (but this mode is a real pain...). And worth to mention: Sub-Selects will repeat the namespaces, which is not wrong but very annoying... — Shnugo, Aug 27 '19 at 08:05

Create a XML from SQL Server

1 Answers1