I have a couple of tables, Employee and Role. An employee can have multiple roles. In the employee table, the primary key is employee_ID which is a identity field in the table. The role table stores Employee_ID and Role.
I want to insert multiple records using an XML Document. My xml document will look something like this:
<Employee Firstname="Tony" LastName="Montana">
<Role Role="Project Leader"/>
<Employee Firstname="Charles" LastName="Luciano">
<Role Role="Information Architect"/>
<Employee Firstname="Nino" LastName="Brown">
<Role Role="Quality Assurance"/>
<Role Role="Technical Writer"/>
<Role Role="PC Support"/>
I would like to use a stored procedure to insert these records. The stored procedure I created will work for only one record because I am able to get the employee_ID value from @@identity for the insert in role table.
This is where I am stuck, because I would like to get the employee_ID for each of those records for the insert into the Role table.
Thanks in advance for the help.