Avanzando en el proyecto!

Avances

Se ha avanzado mucho en la parte de migración de datos del XML a la base de datos

se han trabajado 3 horas en la migración de datos, todas las inserciones se han hecho de forma masiva y estas a su vez se han optimizado

Ejemplo del Stored Procedure:

CREATE PROCEDURE [dbo].[ARSP_MigrateData]

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

BEGIN TRY

EXEC dbo.ARSP_ClearMovementDebug

IF(EXISTS(SELECT 1 FROM dbo.Loan))

DELETE FROM dbo.Loan;

IF(EXISTS(SELECT 1 FROM dbo.Debtor))

DELETE FROM dbo.Debtor;

IF(EXISTS(SELECT 1 FROM dbo.LoanType))

DELETE FROM dbo.LoanType;

IF(EXISTS(SELECT 1 FROM dbo.BillStatus))

DELETE FROM dbo.BillStatus;

DECLARE @Loan TABLE(ID INT IDENTITY(1,1),OriginalAmount MONEY,Fee MONEY,RemainingTerm INT,NABalance MONEY,ABalance MONEY,MonthAcumInterest FLOAT,Deadline INT,fkLoanType INT,fkDebtor int)

DECLARE @LoanType TABLE(ID INT,Name VARCHAR(50),InterestRate FLOAT,TermMonths INT)

DECLARE @Debtor TABLE(ID INT NOT NULL,Name VARCHAR(50),Code VARCHAR(50))

DECLARE @BillStatus TABLE(ID INT,Name VARCHAR(50))

DECLARE @XML XML

SELECT @XML = BulkColumn

FROM OPENROWSET(BULK 'D:\XMLBasesAR.xml', SINGLE_CLOB) AS x;

INSERT INTO @BillStatus(ID,Name)

SELECT    estado.value('@ID', 'VARCHAR(50)'),

estado.value('@Name', 'VARCHAR(50)')

FROM @XML.nodes('/AccountReceivable') AS x1(AR)

cross apply x1.AR.nodes('./BillPaymentStatus') AS x2(estados)

cross apply x2.estados.nodes('./BillStatus') AS x3(estado)

 

INSERT INTO @LoanType(ID,Name,InterestRate,TermMonths)

SELECT tipo.value('@ID','VARCHAR(50)'),

tipo.value('@Name','VARCHAR(50)'),

tipo.value('@InterestRate','VARCHAR(50)'),

tipo.value('@TermMonths','VARCHAR(50)')

FROM @XML.nodes('/AccountReceivable') AS x1(AR)

cross apply x1.AR.nodes('./LoanTypes') AS x2(tipos)

cross apply x2.tipos.nodes('./LoanType') AS x3(tipo)

INSERT INTO @Debtor(ID,Name,Code)

SELECT      

deudor.value('@ID', 'VARCHAR(50)'),

deudor.value('@Name','VARCHAR(50)'),

deudor.value('@Cedula', 'VARCHAR(50)')

FROM @XML.nodes('/AccountReceivable') AS x1(AR)

cross apply x1.AR.nodes('./Debtors') AS x2(deudores)

cross apply x2.deudores.nodes('./Debtor') AS x3(deudor)

select * from @Debtor

INSERT INTO @Loan(OriginalAmount,Fee,RemainingTerm,NABalance,ABalance,MonthAcumInterest,Deadline,fkLoanType,fkDebtor)

SELECT       

prestamo.value('@OriginalAmount', 'VARCHAR(50)'),

prestamo.value('@Fee', 'VARCHAR(50)'),

(SELECT  LT.TermMonths

 FROM @LoanType LT 

 WHERE LT.ID = prestamo.value('@IDTipPrestamo', 'VARCHAR(50)')),

0,prestamo.value('@OriginalAmount', 'VARCHAR(50)'),

0,prestamo.value('@Deadline', 'VARCHAR(50)'),

prestamo.value('@IDTipPrestamo', 'VARCHAR(50)'),

prestamo.value('@IDPersona', 'VARCHAR(50)')

FROM @XML.nodes('/AccountReceivable') AS x1(AR)

cross apply x1.AR.nodes('./Loans') AS x2(prestamos)

cross apply x2.prestamos.nodes('./Loan') AS x3(prestamo)

SELECT * FROM @Loan

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRANSACTION

INSERT INTO dbo.BillStatus(ID,Name) 

SELECT BS.ID, BS.Name 

FROM @BillStatus BS

INSERT INTO dbo.LoanType(ID,Name,InterestRate,TermMonths)

SELECT LT.ID, LT.Name, LT.InterestRate, LT.TermMonths

FROM @LoanType LT

INSERT INTO dbo.Debtor(ID,Name,Deleted,Code)

SELECT D.ID,D.Name,0,D.Code

FROM @Debtor D

INSERT INTO dbo.Loan(ID,OriginalAmount,Fee,RemainingTerm,NonAppliedBalance,AppliedBalance,MonthAcumInterest,Deadline,FK_LoanType,FK_Debtor,Deleted)

SELECT L.ID,L.OriginalAmount,L.Fee,L.RemainingTerm,L.NABalance,L.ABalance,L.MonthAcumInterest,L.Deadline,L.fkLoanType,L.fkDebtor,0

FROM @Loan L

COMMIT

RETURN 1

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0 

ROLLBACK

PRINT 'Something went wrong!'

RETURN -1 * @@ERROR

END CATCH

END

GO

A su vez se ha creado un Stored Procedure para poder estar limpiando la base de datos ya que los compañeros que hacen el archivo XML por lo general lo hacen con errores y cambios constantes y se tiene que estar borrando los datos en algunas tablas, en esta parte de la base de datos se ha durado 30 minutos

CREATE PROCEDURE [dbo].[ARSP_ClearMovementDebug] 

-- Add the parameters for the stored procedure here

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DELETE FROM dbo.AppliedBalanceMovement

DELETE FROM dbo.NonAppliedBalanceMovement

DELETE FROM dbo.DailyInterestMovement

END

GO