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.