Fecha Inicial : 07/04/2018
Fecha Final : 07/04/2018
Hora Inicio: 10:36 PM
Hora Final: 10:52PM
Horas invertidas(Minutos): 620
Colaboradores: Wilson Lopez
Actividad: Continuación con la creación de la lectura de datos de manera recursiva , creacion de store procedures para dicho propósito
Problemas :
Encontramos un problema al usar indentitys ya que el orden de los grupos es distinto del que debe seguir el siclo , se solicita ayuda a los compañeros de XML para solucionarlo
Codigo
Fecha Final : 07/04/2018
Hora Inicio: 10:36 PM
Hora Final: 10:52PM
Horas invertidas(Minutos): 620
Colaboradores: Wilson Lopez
Actividad: Continuación con la creación de la lectura de datos de manera recursiva , creacion de store procedures para dicho propósito
Problemas :
Encontramos un problema al usar indentitys ya que el orden de los grupos es distinto del que debe seguir el siclo , se solicita ayuda a los compañeros de XML para solucionarlo
Codigo
CREATE PROCEDURE VaciarTablas
AS
BEGIN
EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?';
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
EXEC sp_MSForEachTable 'DELETE FROM ?';
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL';
EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?';
EXEC sp_MSforeachtable @command1 = 'DBCC CHECKIDENT(''?'', RESEED, 0)'
END
GO
CREATE PROCEDURE [dbo].[readXML_studentGroupStateData]
AS
BEGIN
DECLARE @xml xml
DECLARE @hdoc int
BEGIN TRY
SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data\studentGroupStateData.xml', SINGLE_BLOB) as studentGroupStateData(roow)
if (@xml IS NOT NULL)
begin
begin tran
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml
SELECT * INTO #tmp_studentGroupStateData FROM OPENXML(@hDoc, 'XML/studentGroupStateData/studentGroupState')
WITH(
name [nvarchar](50)
)
begin
INSERT INTO EstadoEstudiante(Nombre) SELECT name FROM #tmp_studentGroupStateData
--EXEC dbo.Create_teacherData @Name = name,@Email = email,@Password = password;
end
DROP TABLE #tmp_studentGroupStateData
end
commit tran
return 1 --successful
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
return 0 --failed
END CATCH
END
GO
CREATE PROCEDURE readXML_studentData
AS
BEGIN
DECLARE @xml xml
DECLARE @hdoc int
BEGIN TRY
SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data\studentData.xml', SINGLE_BLOB) as studentData(roow)
if (@xml IS NOT NULL)
begin
begin tran
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml
SELECT * INTO #tmp_studentData FROM OPENXML(@hDoc, 'XML/studentData/student')
WITH(
name [nvarchar](50),
lastName[nvarchar](50),
email [nvarchar](50),
carnet [nvarchar](50),
phone[int]
)
begin
INSERT INTO Estudiante(Nombre,Apellido,Email,Carne,Telefono)SELECT name,lastName,email,carnet,phone FROM #tmp_studentData
--EXEC dbo.Create_teacherData @Name = name,@Email = email,@Password = password;
end
DROP TABLE #tmp_studentData
end
commit tran
return 1 --successful
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
return 0 --failed
END CATCH
END
GO
CREATE PROCEDURE [dbo].[readXML_teacherData]
AS
BEGIN
DECLARE @xml xml
DECLARE @hdoc int
DECLARE @pName nvarchar
DECLARE @pEmail nvarchar
DECLARE @pPassword nvarchar
BEGIN TRY
SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data\teacherData.xml', SINGLE_BLOB) as teacherData(roow)
if (@xml IS NOT NULL)
begin
begin tran
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml
SELECT * INTO #tmp_teacherData FROM OPENXML(@hDoc, 'XML/teacherData/teacher')
WITH(
name [nvarchar](50),
email [nvarchar](50),
password [nvarchar](50)
)
begin
INSERT INTO Profesor (Nombre,Email,contrasenna) SELECT name,email,password FROM #tmp_teacherData
--EXEC dbo.Create_teacherData @Name = name,@Email = email,@Password = password;
end
DROP TABLE #tmp_teacherData
end
commit tran
return 1 --successful
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
return 0 --failed
END CATCH
END
GO
CREATE PROCEDURE readXML_itemData
AS
BEGIN
DECLARE @xml xml
DECLARE @hdoc int
BEGIN TRY
SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data\itemData.xml', SINGLE_BLOB) as itemData(roow)
if (@xml IS NOT NULL)
begin
begin tran
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml
SELECT * INTO #tmp_itemData FROM OPENXML(@hDoc, 'XML/itemData/item')
WITH(
name [nvarchar](50)
)
begin
INSERT INTO Rubros(Nombre)SELECT name FROM #tmp_itemData
--EXEC dbo.Create_teacherData @Name = name,@Email = email,@Password = password;
end
DROP TABLE #tmp_itemData
end
commit tran
return 1 --successful
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
return 0 --failed
END CATCH
END
GO
CREATE PROCEDURE readXML_groupStateData
AS
BEGIN
DECLARE @xml xml
DECLARE @hdoc int
BEGIN TRY
SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data\groupStateData.xml', SINGLE_BLOB) as groupStateData(roow)
if (@xml IS NOT NULL)
begin
begin tran
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml
SELECT * INTO #tmp_groupStateData FROM OPENXML(@hDoc, 'XML/groupStateData/groupState')
WITH(
name [nvarchar](50)
)
begin
INSERT INTO EstadoGrupo(Nombre)SELECT name FROM #tmp_groupStateData
--EXEC dbo.Create_teacherData @Name = name,@Email = email,@Password = password;
end
DROP TABLE #tmp_groupStateData
end
commit tran
return 1 --successful
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
return 0 --failed
END CATCH
END
GO
CREATE PROCEDURE [dbo].[readXML_groupData] @Id_Periodo int
AS
BEGIN
DECLARE @grupcount int
DECLARE @y int
DECLARE @xml xml
DECLARE @hdoc int
BEGIN TRY
SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data\groupData.xml', SINGLE_BLOB) as groupData(roow)
if (@xml IS NOT NULL)
begin
begin tran
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml
SELECT * INTO #tmp_groupData FROM OPENXML(@hDoc, 'XML/groupData/group')
WITH(
groupStateID[int],
teacherID[int],
termID[int],
courseName[nvarchar](50),
code[nvarchar](50)
)
begin
/* Lee el tamaño de la tabla grupo , le suma 1 ya que los ID comienzan en 1 y no en 0
inserta los nuevos datos , y leo el nuevo tamaño de la tabla , ese sera el stop del while
*/
SELECT @y = COUNT(*) FROM Grupo
INSERT INTO Grupo(FK_Estado,FK_Profesor,FK_Periodo,NombreCurso,CodigoGrupo)SELECT groupStateID,teacherID,termID,courseName,code FROM #tmp_groupData WHERE termID = @Id_Periodo
SELECT @grupcount= COUNT(*) FROM Grupo
SET @y=@y+1 ;
WHILE(@y<@grupcount+1)
BEGIN
EXEC readXML_groupItemData @Id_Grupo = @y
EXEC readXML_studentGroupData @Id_Grupo =@y
SET @y = @y+1
END
end
DROP TABLE #tmp_groupData
end
commit tran
return 1 --successful
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
Select ERROR_MESSAGE()
return 0 --failed
END CATCH
END
GO
CREATE PROCEDURE [dbo].[readXML_groupItemData] @Id_Grupo int
AS
BEGIN
DECLARE @xml xml
DECLARE @hdoc int
BEGIN TRY
SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data\groupItemData.xml', SINGLE_BLOB) as groupItemData(roow)
if (@xml IS NOT NULL)
begin
begin tran
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml
SELECT * INTO #tmp_groupItemData FROM OPENXML(@hDoc, 'XML/groupItemData/groupItem')
WITH(
itemID [int],
groupID [int],
percentage [nvarchar](50),
count [nvarchar](50),
constantCount [nvarchar](50)
)
begin
--SELECT * FROM #tmp_groupItemData
INSERT INTO GrupoxRubro(FK_Grupo,FK_Rubro,Valor,EsFijo,Q) SELECT groupID,itemID,percentage,constantCount,count FROM #tmp_groupItemData WHERE groupID = @Id_Grupo
end
DROP TABLE #tmp_groupItemData
end
commit tran
return 1 --successful
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
SELECT ERROR_MESSAGE()
return 0 --failed
END CATCH
END
GO
CREATE PROCEDURE readXML_studentGroupData @Id_Grupo int
AS
BEGIN
DECLARE @xml xml
DECLARE @hdoc int
BEGIN TRY
SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data\studentGroupData.xml', SINGLE_BLOB) as studentGroupData(roow)
if (@xml IS NOT NULL)
begin
begin tran
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml
SELECT * INTO #tmp_studentGroupData FROM OPENXML(@hDoc, 'XML/studentGroupData/studentGroup')
WITH(
totalGrade [nvarchar](50),
groupID [int],
studentID [int],
studentGroupStateID [int]
)
begin
--SELECT * FROM #tmp_studentGroupData
INSERT INTO GrupoxEstudiante(FK_Estudiante,FK_Grupo,NotaAcumulada,FK_Estado) SELECT studentID,groupID,0,2 FROM #tmp_studentGroupData WHERE groupID = @Id_Grupo
end
DROP TABLE #tmp_studentGroupData
end
commit tran
return 1 --successful
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
SELECT ERROR_MESSAGE()
return 0 --failed
END CATCH
END
GO
-----------------------------------------
CREATE PROCEDURE [dbo].[readXML_evaluationData] @Finicial date,@Ffinal date
AS
BEGIN
DECLARE @xml xml
DECLARE @hdoc int
DECLARE @tmp_Date date
BEGIN TRY
SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data\evaluationData.xml', SINGLE_BLOB) as evaluationData(roow)
if (@xml IS NOT NULL)
begin
begin tran
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml
SELECT * INTO #tmp_evaluationData FROM OPENXML(@hDoc,'XML/evaluationData/evaluation')
WITH(
groupItemID [int],
name [nvarchar](50),
date [datetime],
percentage [nvarchar](50),
description [nvarchar](50)
)
begin
SET @tmp_Date = @Finicial
WHILE(@tmp_Date<=@Ffinal)
BEGIN
INSERT INTO Evaluacion(FK_Grupo_Rubro,Nombre,Fecha,ValorPorcentual,Descripcion) SELECT groupItemID,name,date,percentage,description FROM #tmp_evaluationData WHERE (SELECT CONVERT(date, date)) = @tmp_Date
SET @tmp_Date = (SELECT DATEADD(day, 1, @tmp_Date) AS DateAdd)
END
end
DROP TABLE #tmp_evaluationData
end
commit tran
return 1 --successful
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
SELECT ERROR_MESSAGE()
return 0 --failed
END CATCH
END
Comentarios
Publicar un comentario