SQL Server

Insert Signed XML as Base64 Data into Invoice XML

See more TicketBAI Examples

Demonstrates how to insert previously signed XML into another XML as base64 data. SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- We wish to create the following XML:

    -- <?xml version="1.0" encoding="UTF-8" standalone="no"?><lrpficfcsgap:LROEPF140IngresosConFacturaConSGAltaPeticion xmlns:lrpficfcsgap="https://www.batuz.eus/fitxategiak/batuz/LROE/esquemas/LROE_PF_140_1_1_Ingresos_ConfacturaConSG_AltaPeticion_V1_0_2.xsd">
    -- 	<Cabecera>
    -- 		<Modelo>140</Modelo>
    -- 		<Capitulo>1</Capitulo>
    -- 		<Subcapitulo>1.1</Subcapitulo>
    -- 		<Operacion>A00</Operacion>
    -- 		<Version>1.0</Version>
    -- 		<Ejercicio>2022</Ejercicio>
    -- 		<ObligadoTributario>
    -- 			<NIF>79732487C</NIF>
    -- 			<ApellidosNombreRazonSocial>ARRIOLA LEJARDI ANE</ApellidosNombreRazonSocial>
    -- 		</ObligadoTributario>
    -- 	</Cabecera>
    -- 	<Ingresos>		
    -- 		<Ingreso>
    -- 			<TicketBai>PD94bWw...ldEJhaT4=</TicketBai>
    -- 		<Renta>
    -- 			<DetalleRenta>
    -- 				<Epigrafe>197330</Epigrafe>
    -- 			</DetalleRenta>
    -- 		</Renta>
    -- 	</Ingreso>
    -- 		<Ingreso>
    -- 			<TicketBai>PD94bWw...rZXRCYWk+</TicketBai>
    -- 		<Renta>
    -- 			<DetalleRenta>
    -- 				<Epigrafe>197330</Epigrafe>
    -- 				<IngresoAComputarIRPFDiferenteBaseImpoIVA>S</IngresoAComputarIRPFDiferenteBaseImpoIVA>
    -- 				<ImporteIngresoIRPF>400.00</ImporteIngresoIRPF>
    -- 			</DetalleRenta>
    -- 		</Renta>
    -- 	</Ingreso>	
    -- 	</Ingresos>
    -- </lrpficfcsgap:LROEPF140IngresosConFacturaConSGAltaPeticion>

    -- Use this online tool to generate code from sample XML: 
    -- Generate Code to Create XML

    -- First get the signed XML created in these other examples
    -- TicketBAI Sign XML Example 1
    DECLARE @sb1 int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sb1 OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @sb1, 'LoadFile', @success OUT, 'qa_output/signedXml_1.xml', 'utf-8'

    -- TicketBAI Sign XML Example 2
    DECLARE @sb2 int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sb2 OUT

    EXEC sp_OAMethod @sb2, 'LoadFile', @success OUT, 'qa_output/signedXml_2.xml', 'utf-8'

    DECLARE @xml int
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    EXEC sp_OASetProperty @xml, 'Tag', 'lrpficfcsgap:LROEPF140IngresosConFacturaConSGAltaPeticion'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns:lrpficfcsgap', 'https://www.batuz.eus/fitxategiak/batuz/LROE/esquemas/LROE_PF_140_1_1_Ingresos_ConfacturaConSG_AltaPeticion_V1_0_2.xsd'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Cabecera|Modelo', '140'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Cabecera|Capitulo', '1'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Cabecera|Subcapitulo', '1.1'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Cabecera|Operacion', 'A00'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Cabecera|Version', '1.0'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Cabecera|Ejercicio', '2022'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Cabecera|ObligadoTributario|NIF', '79732487C'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Cabecera|ObligadoTributario|ApellidosNombreRazonSocial', 'ARRIOLA LEJARDI ANE'
    EXEC sp_OAMethod @sb1, 'GetEncoded', @sTmp0 OUT, 'base64', 'utf-8'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Ingresos|Ingreso|TicketBai', @sTmp0
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Ingresos|Ingreso|Renta|DetalleRenta|Epigrafe', '197330'
    EXEC sp_OAMethod @sb2, 'GetEncoded', @sTmp0 OUT, 'base64', 'utf-8'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Ingresos|Ingreso[1]|TicketBai', @sTmp0
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Ingresos|Ingreso[1]|Renta|DetalleRenta|Epigrafe', '197330'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Ingresos|Ingreso[1]|Renta|DetalleRenta|IngresoAComputarIRPFDiferenteBaseImpoIVA', 'S'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Ingresos|Ingreso[1]|Renta|DetalleRenta|ImporteIngresoIRPF', '400.00'

    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    EXEC sp_OAMethod @xml, 'SaveXml', @success OUT, 'qa_output/ticketBAI_invoice.xml'

    EXEC @hr = sp_OADestroy @sb1
    EXEC @hr = sp_OADestroy @sb2
    EXEC @hr = sp_OADestroy @xml


END
GO