SQL Server Performance

How to insert DATA from XML to multiple tables (via SPs) having foreign key references between them?

Discussion in 'ALL SQL SERVER QUESTIONS' started by bsethi24, Dec 16, 2011.

  1. bsethi24 New Member

    As we know that we can insert data from XML into SQL Server 2008 tables. I have 2 questions related to data insert from XML: -

    1. Can we insert data from XML to more than one table when they have Foreign key references between them? If yes, then how? Kindly guide.

    2. If answer of first question is Yes, then can we pass XML as value of a Parameter to a Store Procedure? This Store procedure has "Insert queries" to insert data in Foreign key referenced tables.


    To understand what I am looking for below is an example: -


    First Table "School"
    School_Id int (Primary Key & Identity column),
    School_Name varchar (1000),
    Remarks varchar (1000)

    Second Table "Student"
    Student_Id int (Primary Key & Identity column),
    School_Id int (Foreign key reference with School table's School_Id column)
    Student_Name varchar(1000)

    Third Table "Parent"
    Parent_Id int (Primary key & Identity column),
    Student_Id int (Foreign key reference with Student table's Student_Id column)
    Parent_Name varchar (1000)

    Now I have XML to insert data in "School" table & a Store Procedure named "Pr_Student_Parent" to insert data in "Student" and "Parent" tables.

    I need to know the process how to pass "School" table identity column value to STORE PROCEDURE "Pr_Student_Parent" so that first data get inserted into "Student" table and then

    How can we get "Student" table identity column value in "Pr_Student_Parent" Store Procedure to insert data in "Parent" table?

    Is there anyway how to do all this via XML? If yes, kindly explain with example or provide some link which can explain this process?

    Currently, I am inserting all data from XML to a temp table and then run a cursor to insert data into above 3 tables.


    Kindly guide.
    bsethi24, Dec 16, 2011
    #1

  2. FrankKalis Moderator

    I'm not sure I fully understand what you are asking, but it is possible to extract the "parent" data from the XML and populate the parent table from it, get the keys for that newly inserted data and then use these keys along with the "child data" from the XML to populate the child table with it. You would either use a parameter of type xml or maybe nvarchar(MAX) to pass XML data to a procedure. To pass IDENTITY values around, you can use SCOPE_IDENTITY maybe together with an OUTPUT parameter, or use the OUTPUT clause to return a result set from one procedure.
    Pseudocode example:

    CREATE PROCEDURE InsertSchool @XML XML, @Variable int OUTPUT
    AS
    ...
    INSERT INTO ....
    SELECT ... FROM @XML
    SELECT @Variable = SCOPE_IDENTITY;
    GO

    CREATE PROCEDURE Student @XML
    AS
    DECLARE @StudentTABLE (...)
    INSERT INTO ...
    OUTPUT INSERTED.INSERTEDID
    INTO @StudentTABLE ...
    SELECT ... FROM @XML

    and call it like this

    INSERT #temptable EXEC xyz @XML
    FrankKalis, Dec 19, 2011
    #2

Share This Page