Fecha Inicial : 27/03/2018
Fecha Final :  27/03/2018
Hora Inicio: 17:02PM
Hora Final: 19:59
Horas invertidas(Minutos): 180
Colaboradores: Wilson Lopez 

 Actividad: 
Creación de los procedimientos almacenados de creación , así como lectura de archivos xml para agregarlos a sus respectivas tablas.

 Código:
--||||||Estudiante||||||||
--CrearEstudiante
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Create_studentData(@Name nvarchar(50),@lastName nvarchar(50),@Email nvarchar(50),@Carnet nvarchar(50),@Phone int)

AS
BEGIN
    BEGIN TRY
        IF not exists(SELECT * FROM Estudiante WHERE Estudiante.Carne = @Carnet AND Estudiante.Email = @Email)  
        begin
            begin tran
                INSERT INTO Estudiante(Nombre,Apellido,Email,Carne,Telefono) VALUES (@Name,@lastName,@Email,@Carnet,@Phone)     
        end
        commit tran
        return 1
    END TRY
    BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK;
            return 0
    END CATCH
END
GO
--Crete_student_From_XML

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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
5
--||||||Profesor||||||||
--Create_Profesor
CREATE PROCEDURE Create_techerData(@Name nvarchar(50),@Email nvarchar(50), @Password nvarchar(50))
AS
BEGIN
    BEGIN TRY
        IF not exists(SELECT * FROM Profesor WHERE Profesor.Email = @Email) 
        begin
            begin tran
                INSERT INTO Profesor(Nombre,Email,contrasenna) VALUES (@Name,@Email,@Password)      
        end
        commit tran
        return 1    --successfully
    END TRY
    BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK;
            return 0    --Error
    END CATCH

END
GO
--Create_Profesor_From_XML
CREATE PROCEDURE [dbo].[readXML_teacherData]

AS
BEGIN
    DECLARE @xml xml
    DECLARE @hdoc int
    DECLARE @pName nvarchar
    DECLARE @pEmail nvarchar
    DECLARE @pPassword nvarchar
    BEGIN TRY
    --PONER EL ARCHIVO EN LA RUTA C:\data\SQL-Data\teacherData.xml
        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
--||||||EstadoEstudiante||||||||
--Create_EstadoEstudiante
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE Create_studentGroupStateData(@Estado nvarchar(50))
AS
BEGIN
    BEGIN TRY
        IF not exists(SELECT * FROM EstadoEstudiante WHERE EstadoEstudiante.Nombre = @Estado)   
        begin
            begin tran
                INSERT INTO EstadoEstudiante(Nombre) VALUES (@Estado)   
        end
        commit tran
        return 1
    END TRY
    BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK;
            return 0
    END CATCH
    
END
GO
--Create_EstadoEstudiante_From_XML
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

Fuentes:
https://www.c-sharpcorner.com/UploadFile/rohatash/openxml-function-in-sql-server-2012/
http://www.informit.com/articles/article.aspx?p=26499
http://www.informit.com/articles/article.aspx?p=26499

Comentarios

Publicar un comentario