XML for Database Columns

2

I have a table that holds an XML in one of its columns, however I need to do a select by placing each XML item in a column. The problem is that I do not want to create another table, I want something, even if it is temporary, just to make it easier to access the data.

Example:

<?xml version="1.0" encoding="utf-16"?>
<Viagem xmlns:xsd="http://www.w3.org/2001/XMLSchema" mlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <Identificador>111111</Identificador>
   <EstimativaInicio>2015-09-07T23:46:48</EstimativaInicio>
   <EstimativaFim>2015-09-07T23:46:48</EstimativaFim>
   <IdCliente>000</IdCliente>
   <IdEmbarcador>000</IdEmbarcador>
   <IdUnidade xsi:nil="true" />
   <CNPJUnidade>00000000/0000-00</CNPJUnidade>
   <IdCooperativa xsi:nil="true" />
   <IdTransportadora xsi:nil="true" />
   <TemperaturaMinima>0</TemperaturaMinima>
   <TemperaturaMaxima>0</TemperaturaMaxima>
   <Motorista>JOAO DA SILVA</Motorista>
   <Placa>ITO-7457</Placa>
   <FoneMotorista>0000000000</FoneMotorista>
   <Tipo>AAA</Tipo>
   <Peso>0</Peso>
   <Valor>0</Valor>
   <Cubagem>0</Cubagem>
   <ViagemPrioritaria>1</ViagemPrioritaria>
   <Entregas>
       <Entrega>
           <Sequencia>2</Sequencia>
           <Codigo>000000000/0000-00</Codigo>
           <Referencia>AA COMERCIAL DE ALIMENTOS LTDA</Referencia>
           <Latitude>-00.0000</Latitude>
           <Longitude>-00.0000</Longitude>
           <EstimativaInicio>2015-09-07T00:00:00</EstimativaInicio>
           <EstimativaFim>2015-09-07T10:06:00</EstimativaFim>
           <Peso>28.06</Peso>
           <Cubagem>0</Cubagem>
           <Valor>201.34</Valor>
           <Endereco>RUA, 000</Endereco>
           <Bairro>CENTRO</Bairro>
           <Cidade>CIDADE</Cidade>
       </Entrega>
   </Entregas>
</Viagem>

This XML above should return me a table with the following columns:

  • Identifier;
  • HomeStart;
  • EndNote;
  • CustomerID;
  • Stackoverflow;
  • CNPJUnidade;
  • Minimal Temperature;
  • Maximum Temperature;
  • Driver;
  • Plate;
  • Mobile Phone;
  • Type;
  • Weight;
  • Value;
  • Cubing; and
  • Priority Travel.

The columns of the Deliverables tag should be ignored as it will not be used at this point.

Does anyone know how to do this?

    
asked by anonymous 08.10.2015 / 20:28

1 answer

1

For testing purposes, place your XML inside a variable for testing. Notice that I've edited namespaces to work:

declare @data xml = '<Viagem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
       <Identificador>111111</Identificador>
       <EstimativaInicio>2015-09-07T23:46:48</EstimativaInicio>
       <EstimativaFim>2015-09-07T23:46:48</EstimativaFim>
       <IdCliente>000</IdCliente>
       <IdEmbarcador>000</IdEmbarcador>
       <IdUnidade xsi:nil="true" />
       <CNPJUnidade>00000000/0000-00</CNPJUnidade>
       <IdCooperativa xsi:nil="true" />
       <IdTransportadora xsi:nil="true" />
       <TemperaturaMinima>0</TemperaturaMinima>
       <TemperaturaMaxima>0</TemperaturaMaxima>
       <Motorista>JOAO DA SILVA</Motorista>
       <Placa>ITO-7457</Placa>
       <FoneMotorista>0000000000</FoneMotorista>
       <Tipo>AAA</Tipo>
       <Peso>0</Peso>
       <Valor>0</Valor>
       <Cubagem>0</Cubagem>
       <ViagemPrioritaria>1</ViagemPrioritaria>
       <Entregas>
           <Entrega>
               <Sequencia>2</Sequencia>
               <Codigo>000000000/0000-00</Codigo>
               <Referencia>AA COMERCIAL DE ALIMENTOS LTDA</Referencia>
               <Latitude>-00.0000</Latitude>
               <Longitude>-00.0000</Longitude>
               <EstimativaInicio>2015-09-07T00:00:00</EstimativaInicio>
               <EstimativaFim>2015-09-07T10:06:00</EstimativaFim>
               <Peso>28.06</Peso>
               <Cubagem>0</Cubagem>
               <Valor>201.34</Valor>
               <Endereco>RUA, 000</Endereco>
               <Bairro>CENTRO</Bairro>
               <Cidade>CIDADE</Cidade>
           </Entrega>
       </Entregas>
    </Viagem>';

select @data.value('(/Viagem/Identificador/node())[1]', 'nvarchar(max)') as Identificador, 
    @data.value('(/Viagem/EstimativaInicio/node())[1]', 'nvarchar(max)') as EstimativaInicio,
    @data.value('(/Viagem/EstimativaFim/node())[1]', 'nvarchar(max)') as EstimativaFim,
    @data.value('(/Viagem/IdCliente/node())[1]', 'nvarchar(max)') as IdCliente,
    @data.value('(/Viagem/IdEmbarcador/node())[1]', 'nvarchar(max)') as IdEmbarcador,
    @data.value('(/Viagem/CNPJUnidade/node())[1]', 'nvarchar(max)') as CNPJUnidade,
    @data.value('(/Viagem/TemperaturaMinima/node())[1]', 'nvarchar(max)') as TemperaturaMinima,
    @data.value('(/Viagem/TemperaturaMaxima/node())[1]', 'nvarchar(max)') as TemperaturaMaxima,
    @data.value('(/Viagem/Motorista/node())[1]', 'nvarchar(max)') as Motorista,
    @data.value('(/Viagem/Placa/node())[1]', 'nvarchar(max)') as Placa,
    @data.value('(/Viagem/FoneMotorista/node())[1]', 'nvarchar(max)') as FoneMotorista,
    @data.value('(/Viagem/Tipo/node())[1]', 'nvarchar(max)') as Tipo,
    @data.value('(/Viagem/Peso/node())[1]', 'nvarchar(max)') as Peso,
    @data.value('(/Viagem/Valor/node())[1]', 'nvarchar(max)') as Valor,
    @data.value('(/Viagem/Cubagem/node())[1]', 'nvarchar(max)') as Cubagem,
    @data.value('(/Viagem/ViagemPrioritaria/node())[1]', 'nvarchar(max)') as ViagemPrioritaria;
    
08.10.2015 / 21:29