XML for temporary table records

Question

Last week I I asked Here on how to place each XML tag in a database column. Now I came across a situation close to that but with a plus, my XML comes from the database, and sometimes will come not only 1 record, but more than one.

In the code below the return of the sql being set to the variable @xml returns 60 records, or 60 different Xmls.

How do I put each XML in a record of mine @TabelaTemporaria?

DECLARE @DataIni DATETIME = '2015-09-07 23:00:00',
    @DataFim DATETIME = '2015-09-07 23:59:59',
    @xml XML;
DECLARE @TabelaTemporaria TABLE (Identificador VARCHAR(MAX), EstimativaInicio VARCHAR(MAX), EstimativaFim VARCHAR(MAX), IdCliente VARCHAR(MAX), IdEmbarcador VARCHAR(MAX),
                    CNPJUnidade VARCHAR(MAX), TemperaturaMinima VARCHAR(MAX), TemperaturaMaxima VARCHAR(MAX), Motorista VARCHAR(MAX), Placa VARCHAR(MAX),
                    FoneMotorista VARCHAR(MAX), Tipo VARCHAR(MAX), Peso VARCHAR(MAX), Valor VARCHAR(MAX), Cubagem VARCHAR(MAX), ViagemPrioritaria VARCHAR(MAX), Id INT, 
                    TripId INT, CreateDateTime DATETIME);           
SET @xml = ( SELECT RIGHT(TripXML, LEN(TripXML)-CHARINDEX('?>', TripXML)-1) 
            FROM AuditDB.dbo.TripsXML (NOLOCK) 
            WHERE (CreateDateTime BETWEEN @DataIni AND @DataFim) 
        )   
INSERT INTO @TabelaTemporaria (Identificador, EstimativaInicio, EstimativaFim, IdCliente, IdEmbarcador, CNPJUnidade, TemperaturaMinima, TemperaturaMaxima,
                            Motorista, Placa, FoneMotorista, Tipo, Peso, Valor, Cubagem, ViagemPrioritaria, Id, TripId, CreateDateTime)     
SELECT  @xml.value('(/Viagem/Identificador/node())[1]', 'nvarchar(max)') AS Identificador,
    @xml.value('(/Viagem/EstimativaInicio/node())[1]', 'nvarchar(max)') AS EstimativaInicio,
    @xml.value('(/Viagem/EstimativaFim/node())[1]', 'nvarchar(max)') AS EstimativaFim,
    @xml.value('(/Viagem/IdCliente/node())[1]', 'nvarchar(max)') AS IdCliente,
    @xml.value('(/Viagem/IdEmbarcador/node())[1]', 'nvarchar(max)') AS IdEmbarcador,
    @xml.value('(/Viagem/CNPJUnidade/node())[1]', 'nvarchar(max)') AS CNPJUnidade,
    @xml.value('(/Viagem/TemperaturaMinima/node())[1]', 'nvarchar(max)') AS TemperaturaMinima,
    @xml.value('(/Viagem/TemperaturaMaxima/node())[1]', 'nvarchar(max)') AS TemperaturaMaxima,
    @xml.value('(/Viagem/Motorista/node())[1]', 'nvarchar(max)') AS Motorista,
    @xml.value('(/Viagem/Placa/node())[1]', 'nvarchar(max)') AS Placa,
    @xml.value('(/Viagem/FoneMotorista/node())[1]', 'nvarchar(max)') AS FoneMotorista,
    @xml.value('(/Viagem/Tipo/node())[1]', 'nvarchar(max)') AS Tipo,
    @xml.value('(/Viagem/Peso/node())[1]', 'nvarchar(max)') AS Peso,
    @xml.value('(/Viagem/Valor/node())[1]', 'nvarchar(max)') AS Valor,
    @xml.value('(/Viagem/Cubagem/node())[1]', 'nvarchar(max)') AS Cubagem,
    @xml.value('(/Viagem/ViagemPrioritaria/node())[1]', 'nvarchar(max)') AS ViagemPrioritaria,
    Id,
    TripId,
    CreateDateTime
FROM AuditDB.dbo.TripsXML (NOLOCK) 
WHERE (CreateDateTime BETWEEN @DataIni AND @DataFim)

Answer 1

I think it doesn’t change much. The difference is that you won’t put the value in the variable, and you’ll need to make a cast in the column as follows:

DECLARE @DataIni DATETIME = '2015-09-07 23:00:00',
    @DataFim DATETIME = '2015-09-07 23:59:59';

DECLARE @TabelaTemporaria TABLE (Identificador VARCHAR(MAX), EstimativaInicio VARCHAR(MAX), EstimativaFim VARCHAR(MAX), IdCliente VARCHAR(MAX), IdEmbarcador VARCHAR(MAX),
                    CNPJUnidade VARCHAR(MAX), TemperaturaMinima VARCHAR(MAX), TemperaturaMaxima VARCHAR(MAX), Motorista VARCHAR(MAX), Placa VARCHAR(MAX),
                    FoneMotorista VARCHAR(MAX), Tipo VARCHAR(MAX), Peso VARCHAR(MAX), Valor VARCHAR(MAX), Cubagem VARCHAR(MAX), ViagemPrioritaria VARCHAR(MAX), Id INT, TripId INT, CreateDateTime DATETIME);           

INSERT INTO @TabelaTemporaria (Identificador, EstimativaInicio, EstimativaFim, IdCliente, IdEmbarcador, CNPJUnidade, TemperaturaMinima, TemperaturaMaxima,
                            Motorista, Placa, FoneMotorista, Tipo, Peso, Valor, Cubagem, ViagemPrioritaria, Id, TripId, CreateDateTime)     
SELECT  
    campoXml.value('(/Viagem/Identificador/node())[1]', 'nvarchar(max)') AS Identificador,
    campoXml.value('(/Viagem/EstimativaInicio/node())[1]', 'nvarchar(max)') AS EstimativaInicio,
    campoXml.value('(/Viagem/EstimativaFim/node())[1]', 'nvarchar(max)') AS EstimativaFim,
    campoXml.value('(/Viagem/IdCliente/node())[1]', 'nvarchar(max)') AS IdCliente,
    campoXml.value('(/Viagem/IdEmbarcador/node())[1]', 'nvarchar(max)') AS IdEmbarcador,
    campoXml.value('(/Viagem/CNPJUnidade/node())[1]', 'nvarchar(max)') AS CNPJUnidade,
    campoXml.value('(/Viagem/TemperaturaMinima/node())[1]', 'nvarchar(max)') AS TemperaturaMinima,
    campoXml.value('(/Viagem/TemperaturaMaxima/node())[1]', 'nvarchar(max)') AS TemperaturaMaxima,
    campoXml.value('(/Viagem/Motorista/node())[1]', 'nvarchar(max)') AS Motorista,
    campoXml.value('(/Viagem/Placa/node())[1]', 'nvarchar(max)') AS Placa,
    campoXml.value('(/Viagem/FoneMotorista/node())[1]', 'nvarchar(max)') AS FoneMotorista,
    campoXml.value('(/Viagem/Tipo/node())[1]', 'nvarchar(max)') AS Tipo,
    campoXml.value('(/Viagem/Peso/node())[1]', 'nvarchar(max)') AS Peso,
    campoXml.value('(/Viagem/Valor/node())[1]', 'nvarchar(max)') AS Valor,
    campoXml.value('(/Viagem/Cubagem/node())[1]', 'nvarchar(max)') AS Cubagem,
    campoXml.value('(/Viagem/ViagemPrioritaria/node())[1]', 'nvarchar(max)') AS ViagemPrioritaria,
    Id,
    TripId,
    CreateDateTime
FROM (
    SELECT CAST(CAST(RIGHT(TripXML, LEN(TripXML)-CHARINDEX('?>', TripXML)-1)  AS NTEXT) AS XML) as campoXml,
    Id,
    TripId,
    CreateDateTime
    FROM AuditDB.dbo.TripsXML (NOLOCK) 
    WHERE (CreateDateTime BETWEEN @DataIni AND @DataFim)
) Tabela

Consider that this cast double:

SELECT CAST(CAST(RIGHT(TripXML, LEN(TripXML)-CHARINDEX('?>', TripXML)-1)  AS NTEXT) AS XML) as campoXml

It must happen if your column is not of the XML type. If it is, you do not need.