How to insert values into a table depending of the XML file attribute with xQuery in SQL Server?

Question

I want to insert the first attributes of the XML always in the same table, but depending of the value of last the XML attributes I have to insert to another table.

I have in mind to something like this. But obviously it is wrong.

CREATE OR ALTER PROCEDURE [dbo].[spCargarDatosCC]
AS
BEGIN
SET NOCOUNT ON;

-- VARIABLES --
DECLARE @CCobro xml

BEGIN TRY
    SELECT @CCobro = CC
    FROM OPENROWSET (Bulk 'D:\Base de datos\FacturacionMunicipal_BD\Base de Datos\XML\Concepto_de_Cobro.xml', Single_BLOB) AS CCobro(CC)

    INSERT INTO CCobro (ID, Nombre, TasaInteresMoratorio, DiaEmisionRecibo, QDiasVencimiento, EsImpuesto, EsRecurrente, EsFijo, TipoCC, Activo)
    SELECT c.value('@id','INT') AS ID
        , c.value('@Nombre','VARCHAR(100)') AS Nombre
        , c.value('@TasaInteresMoratoria','DECIMAL(10,2)') AS TasaInteresMoratorio
        , c.value('@DiaCobro','TINYINT') AS DiaEmisionRecibo
        , c.value('@QDiasVencimiento','TINYINT') AS QDiasVencimiento
        , c.value('@EsImpuesto','VARCHAR(10)') AS EsImpuesto 
        , c.value('@EsRecurrente','VARCHAR(10)') AS EsRecurrente  
        , c.value('@EsFijo','VARCHAR(10)') AS EsFijo  
        , c.value('@TipoCC','VARCHAR(10)') AS TipoCC
        , 1 AS Activo

        -- the code from above to here works as I want

        -- THIS PART IS WHAT I WANT TO INSERT CONDITIONALLY 

        if(c.value('@Monto', 'MONEY') != NULL) then
            insert into CCobro_MontoFijo (ID, MontoFijo) --CCobro_MontoFijo is a table that references CCobro
            SELECT c.value('@id', 'INT') AS ID
            , c.value('@Monto', 'MONEY') AS MontoFijo
        if(c.value('@ValorM3', 'MONEY') != NULL) then
            insert into CCobro_ConsumoAgua(ID, ConsumoM3)  --CCobro_ConsumoAgua is a table that references CCobro
            SELECT c.value('@id', 'INT') AS ID
            , c.value('@ValorM3', 'MONEY') AS ConsumoM3
        if(c.value('@ValorPorcentaje', 'REAL') != NULL) then
            insert into CCobro_InteresMoratorio(ID, Valor_Porcentual) --CCobro_InteresMoratorio is a table that references CCobro
            SELECT c.value('@id', 'INT') AS ID
            , c.value('@ValorPorcentaje', 'REAL') AS Valor_Porcentual

    FROM @CCobro.nodes('/Conceptos_de_Cobro/conceptocobro') AS t(c);



END TRY

BEGIN CATCH
    return @@ERROR * -1
END CATCH

END

XML File that I want to read. Notice that the last attributes sometimes are NULL, because they depends on TipoCC atributte.

<Conceptos_de_Cobro>
    <conceptocobro id="1" Nombre="Agua" DiaCobro="25" QDiasVencimiento="4" EsImpuesto="No" EsRecurrente="Si" EsFijo="No" TasaInteresMoratoria="5.2" TipoCC="CC Consumo" Monto="" ValorM3="500" ValorPorcentaje="" />
    <conceptocobro id="2" Nombre="Patente Licores" DiaCobro="8" QDiasVencimiento="12" EsImpuesto="Si" EsRecurrente="No" EsFijo="No" TasaInteresMoratoria="2.6" TipoCC="CC Fijo" Monto="50000" ValorM3="" ValorPorcentaje="" />
    <conceptocobro id="3" Nombre="impuesto Propiedad" DiaCobro="8" QDiasVencimiento="12" EsImpuesto="Si" EsRecurrente="No" EsFijo="Si" TasaInteresMoratoria="1.5" TipoCC="CC Porcentual" Monto="" ValorM3="" ValorPorcentaje="0.25" />
    <conceptocobro id="4" Nombre="Recolectar Basura" DiaCobro="4" QDiasVencimiento="5" EsImpuesto="No" EsRecurrente="Si" EsFijo="Si" TasaInteresMoratoria="2.2" TipoCC="CC Fijo" Monto="3000" ValorM3="" ValorPorcentaje="" />
    <conceptocobro id="5" Nombre="Mantenimiento de Parques" DiaCobro="3" QDiasVencimiento="7" EsImpuesto="Si" EsRecurrente="Si" EsFijo="Si" TasaInteresMoratoria="0.87" TipoCC="CC Fijo" Monto="970" ValorM3="" ValorPorcentaje="" />
    <conceptocobro id="6" Nombre="Reconexion de Agua" DiaCobro="15" QDiasVencimiento="10" EsImpuesto="No" EsRecurrente="No" EsFijo="Si" TasaInteresMoratoria="3.8" TipoCC="CC Fijo" Monto="780" ValorM3="" ValorPorcentaje="" />
    <conceptocobro id="7" Nombre="Impuesto a la Renta" DiaCobro="5" QDiasVencimiento="15" EsImpuesto="Si" EsRecurrente="Si" EsFijo="No" TasaInteresMoratoria="12.6" TipoCC="CC Porcentual" Monto="" ValorM3="" ValorPorcentaje="0.15" />
    <conceptocobro id="8" Nombre="Mantenimiento de Parques" DiaCobro="7" QDiasVencimiento="6" EsImpuesto="No" EsRecurrente="Si" EsFijo="Si" TasaInteresMoratoria="0.32" TipoCC="CC Fijo" Monto="7850" ValorM3="" ValorPorcentaje="" />
    <conceptocobro id="9" Nombre="Aseo de sitios Publico" DiaCobro="1" QDiasVencimiento="10" EsImpuesto="No" EsRecurrente="Si" EsFijo="Si" TasaInteresMoratoria="7.8" TipoCC="CC Fijo" Monto="580" ValorM3="" ValorPorcentaje="" />
</Conceptos_de_Cobro>

Yitzhak Khabinsky · Accepted Answer · 2020-06-01T21:59:37.747

Each INSERT statement into a different table should be SQL statement on its own.

For each if ... then statement you need to use the following approach.

The WHERE clause will take care of missing values after the conversion into MONEY data type inside the CTE.

SQL

;WITH rs AS
(
    SELECT c.value('@id','INT') AS ID
        , c.value('@Monto','MONEY') AS MontoFijo
    FROM @CCobro.nodes('/Conceptos_de_Cobro/conceptocobro') AS t(c)
)
INSERT INTO CCobro_MontoFijo (ID, MontoFijo)
SELECT ID, MontoFijo FROM rs
WHERE rs.Monto > 0;

How to insert values into a table depending of the XML file attribute with xQuery in SQL Server?

1 Answers1