Inserting Data in SQL from XML file using Stored Proceduer.

This Post will explain how we can upload data in sql server after reading an XML file.
uses:
1. Importing of data to sql server for application like Excel can be done with help of XML conversion of Excel Data and then Inserting it using this process to upload in the sql database.

2.Other XML Data Generated by application can be inserted in to the data base at once hence reducing network traffic for slow networks.

example:
-- below is the syntax to Create a Stored Procedure for Insertion of data in to the table with XML data
Create Procedure prUpload_XML
@P_XML XML=null
AS
BEGIN
insert into table_Name
(
Column1,
Column2,
Column3
)
SELECT
T.Item.value('(@Column1)[1]','nvarchar(50)'),
T.Item.value('(@Column2)[1]','nvarchar(10)'),
T.Item.value('(@Column3)[1]','nvarchar(50)')
 FROM    @P_XML.nodes('/items/item') AS T ( Item )

--Here items is the name of XML file uploaded and item is the tag for each row in the XML.

Conditional Insert Update of Row Using "IF Exists" in sql.

Same Stored Procedure Can be used to Insert and Update the Row by checking if the data already exists in the table. "If Exists" Key word checks the row if already exists in the Table.
 syntax:
if exists(select *  from tablename where column1=@column1 and column2=@column2 )
begin
--UPdate statement should be written here.
end
else
begin
--Insert statement should be written here.
end

here, "where" condition in the exists method should should uniquely identify the row data. Also in case of Update Statement the where condition should uniquely identify the row as it will update the data of the row satisfying the condition.

Single Stored procedure for insert and update is very important in case of same GUI for insertion  and updation of data. This Type of Interface are very User Friendly and efficient when managed perfectly.


 ex:
CREATE PROCEDURE [dbo].[prInsert_BudgetMaster]
            @BudgetSubHeadID as int =null
           ,@Amount as float=null
           ,@DateFrom as datetime =null          
           ,@DateTo as datetime =null
           ,@CreatedByID as int =null
           ,@CreatedDate as datetime =null
           ,@ModifiedByID as int =null
           ,@ModifiedDate as datetime =null
           ,@ChequeAmount as float=null
           ,@CashierByID as int =null
           ,@SubmittedByID as int =null
           ,@SubmittedPositionID as int =null
           ,@SubmittedDate as datetime =null
           ,@ApprovedByID as int =null
           ,@ApprovedPositionID as int =null
           ,@ApprovedDate as datetime =null
           ,@Remarks as nvarchar(1024) =null
           ,@Status  as nvarchar(50) =null
            ,@BudgetHeadMasterID as int    =null
            ,@ProjectMasterID as int=null
            ,@SourceMasterID as int=null
            ,@ProgramMasterID as int=null
            ,@ReleasedDate date=null
            ,@gov_BudgetSubHeadID as int=null
            ,@fiscalYearID as int =null
            ,@LedgerMasterID as int =null
            ,@SubledgerID as int =null
            ,@AllocationType as nvarchar(250)=null,
            @gov_FundTypeForForeignGrants as int=null
            ,@officeFacultyid int=null

AS
BEGIN
Declare @fiscalYearIDdb int=(select FiscalyearID from tblfiscalyearmaster where active=1 and running=1)
Declare @DateFromdb date=(select StartDate from tblfiscalyearmaster where active=1 and running=1)
Declare @DateTodb date=(select EndDate from tblfiscalyearmaster where active=1 and running=1)

if exists ( select tbl_BudgetHeadMaster .BudgetHeadMasterID  as ID,Code,BudgetHeadMasterName ,Amount,DateFrom,DateTo,FiscalYearID,AllocationType,gov_budgetSubheadId from tbl_BudgetHeadMaster 
left outer join tbl_budgetmaster on tbl_BudgetHeadMaster .BudgetHeadMasterID=tbl_budgetmaster.BudgetHeadMasterID
where (isnull(@gov_budgetsubheadid,'')='' or gov_budgetSubheadId=@gov_budgetsubheadid)
and fiscalyearid=@fiscalYearIDdb and AllocationType=@AllocationType and
(isnull(@SourceMasterID,'')='' or SourceMasterID=@SourceMasterID)
and gov_FundTypeForForeignGrants=@gov_FundTypeForForeignGrants
and tbl_BudgetHeadMaster.BudgetHeadMasterID =@BudgetHeadMasterID
and ( ISNULL(@officeFacultyid,'')='' or tbl_BudgetMaster .officefacultyid=@officeFacultyid )
)
begin
update tbl_budgetmaster
set Amount=@Amount,DateFrom=@DateFrom,DateTo=@DateTo,
ModifiedDate=current_timestamp,ModifiedByID=@ModifiedByID,
 ReleasedDate=@ReleasedDate

where (isnull(@gov_budgetsubheadid,'')='' or gov_budgetSubheadId=@gov_budgetsubheadid)
and fiscalyearid=@fiscalYearIDdb and AllocationType=@AllocationType and
(isnull(@SourceMasterID,'')='' or SourceMasterID=@SourceMasterID)
and gov_FundTypeForForeignGrants=@gov_FundTypeForForeignGrants
and tbl_budgetmaster.BudgetHeadMasterID =@BudgetHeadMasterID
and ( ISNULL(@officeFacultyid,'')='' or tbl_BudgetMaster .officefacultyid=@officeFacultyid )
end
else
begin

INSERT INTO tbl_BudgetMaster
                        (BudgetSubHeadID, Amount, DateFrom, DateTo, CreatedByID, CreatedDate,  ChequeAmount, CashierByID,
                        SubmittedByID, SubmittedPositionID, SubmittedDate, ApprovedByID, ApprovedPositionID, ApprovedDate, Remarks, Status, BudgetHeadMasterID,
                        ProgramMasterID, SourceMasterID, ProjectMasterID, ReleasedDate,gov_BudgetSubHeadID,FiscalYearID ,AllocationType,gov_FundTypeForForeignGrants,officefacultyid )
VALUES      (@BudgetSubHeadID,@Amount,@DateFrom,@DateTo,@CreatedByID,current_timestamp,@ChequeAmount,@CashierByID,
                    @SubmittedByID,@SubmittedPositionID,@SubmittedDate,@ApprovedByID,@ApprovedPositionID,@ApprovedDate,@Remarks,@Status,@BudgetHeadMasterID,
                    @ProgramMasterID,@SourceMasterID,@ProjectMasterID,@ReleasedDate,@gov_BudgetSubHeadID,@fiscalYearIDdb,@AllocationType,@gov_FundTypeForForeignGrants,@officeFacultyid )
end
END

Recursion Using Stored Procedure and executing it

Recursion can be achieved using Stored Procedure. In this example root of the child can be obtained by using such method of Recursion.
here same stored procedure is called by itself using exec method passing parameters which makes it to call itself recursively.

This type of recursion is very useful in case of parent child hierarchical data where we have to get the root or origin of the data.
few example cases are:
1. hierarchical structure of menu.
2. hierarchical ledger where the one ledger should be precisely have its parent defined.
3. categorization of data in parent child relation.

example:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Gautam>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
Create PROCEDURE GetRoot
  
    @Component as int
    ,@Root as int OUTPUT
AS
BEGIN
if ((select ParenID  from tbl_BudgetHeadMaster where BudgetHeadMasterID =@Component )is not null)
begin
    set  @Root= (select ParenID  from tbl_BudgetHeadMaster where BudgetHeadMasterID =@Component and ParenID is not null)
    exec GetRoot @Root,@Root   -----Calls itself
  
    end
    else
    begin
    set @Root= @Component
    select @Root
    end
END
GO

---to run the Procedure
  exec getroot 117,@Root =@id  output;