Insert into multiple tables via XML

Last Post 17 Jun 2002 09:31 AM by Bharilal. 0 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Bharilal
New Member
New Member

--
17 Jun 2002 09:31 AM
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:

N'<ROOT>
<Employee Firstname="Tony" LastName="Montana">
<Role Role="Project Leader"/>
<Role Role="Developer"/>
</Employee>
<Employee Firstname="Charles" LastName="Luciano">
<Role Role="Information Architect"/>
</Employee>
<Employee Firstname="Nino" LastName="Brown">
<Role Role="Quality Assurance"/>
<Role Role="Technical Writer"/>
<Role Role="PC Support"/>
</Employee>
</ROOT>'

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.
You are not authorized to post a reply.

Acceptable Use Policy