none
SQL server XML problem RRS feed

  • Question

  • Hi guys, i need some help

    i got this XML

    DECLARE @xml XML;SELECT  @xml = (SELECT TOP(1) codigo, Numero, fecha, transportista, chofer FROM @tblFOR XML PATH(''), TYPE, ROOT ('root'));



    SELECT @xml.query('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">    <soapenv:Body>        <CrearViaje4 xmlns:unis="http://unisolutions.com.ar/">{for $x in /rootreturn $x/*}</CrearViaje4></soapenv:Body></soapenv:Envelope>');


    in the line <CrearViaje4 xmlns:unis="http://unisolutions.com.ar/">
    i just need <CrearViaje4 xmlns="http://unisolutions.com.ar/">
    but if i delete :unis
    when i exectute my sentece it doesnt show the information 

    Wednesday, August 28, 2019 3:28 PM

Answers

  • Hi Antonio,

    So you need a default namespace.

    Here is how to handle it.

    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(DEFAULT 'http://unisolutions.com.ar/')
    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="http://unisolutions.com.ar/">
              <soapenv:Header/>
              <soapenv:Body>
    		  <CrearViaje4 xmlns="http://unisolutions.com.ar/">
    {
    for $x in /root/row
    return $x/*
    }</CrearViaje4>
    </soapenv:Body>
    </soapenv:Envelope>');

    Output:
    <soapenv:Envelope xmlns="http://unisolutions.com.ar/" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
      <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>
    • Marked as answer by Antonioll Wednesday, August 28, 2019 4:12 PM
    Wednesday, August 28, 2019 3:37 PM