Add header in a XML header SQL Server

Question

I have this query

  Select  Top 1 codigo,Numero, fecha,transportista,chofer 
  from VIAJES_HEADER
     for xml path(''), ROOT ('CrearViaje4');

and i get this result:

<CrearViaje4>
  <codigo>X03VAGDVDGR</codigo>
  <Numero>186714</Numero>
  <fecha>2018-02-22</fecha>
  <transportista>X03FGLGQDBM</transportista>
  <chofer>X03FHEXAWEW</chofer>
</CrearViaje4>

How can add a fixed text? i mean the header of the XML for example:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
        xmlns:unis="http://unisolutions.com.ar/">
          <soapenv:Header/>
          <soapenv:Body>
             <CrearViaje4 xmlns="http://unisolutions.com.ar/">
             <codigo>X03VAGDVDGR</codigo>
            <Numero>186714</Numero>
            <fecha>2018-02-22</fecha>
            <transportista>X03FGLGQDBM</transportista>
            <chofer>X03FHEXAWEW</chofer>
        </CrearViaje4>
     </soapenv:Body>
    </soapenv:Envelope>
    </soapenv:Envelope>

Answer 1

As usual SQL Server XQuery FLWOR expression is handy to add SOAP Envelope and namespaces.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE(
    ID INT IDENTITY(1,1) PRIMARY KEY
    , codigo VARCHAR(20)
    , Numero INT
    , fecha DATE
    , transportista VARCHAR(20)
    , chofer VARCHAR(20)
);
INSERT INTO @tbl
VALUES
('X03VAGDVDGR', 186714, '2018-02-22', 'X03FGLGQDBM', 'X03FHEXAWEW')
-- DDL and sample data population, end

DECLARE @xml XML;

;WITH XMLNAMESPACES('http://unisolutions.com.ar/' AS unis)
SELECT  @xml = (
SELECT TOP(1) codigo, Numero, fecha, transportista, chofer 
FROM @tbl
FOR XML PATH('row'), TYPE, ROOT ('root'));

SELECT @xml.query('
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
        xmlns:unis="http://unisolutions.com.ar/">
          <soapenv:Header/>
          <soapenv:Body>
          <CrearViaje4 xmlns:unis="http://unisolutions.com.ar/">
{
for $x in /root/row
return $x/*
}</CrearViaje4>
</soapenv:Body>
</soapenv:Envelope>');