Reputation: 8990
I am passing XML to my stored procedure and inserting it into a table. If I am passing multiple nodes, is all of that happening before it gets to the Something Else
comment?
For example, if my XML contained 6 vehicle nodes, this insert statement will run 6 times. Do all of those run before reaching the something else
line?
The reason I ask is because I want to insert data from this XML into a separate table as well using the lastInsertedID from the code below.
Can this be done?
INSERT INTO licensePlates (carColor, carModel, licensePlate, empID, dateAdded)
SELECT ParamValues.x2.value('color[1]', 'VARCHAR(100)'),
ParamValues.x2.value('model[1]', 'VARCHAR(100)'),
ParamValues.x2.value('licensePlate[1]', 'VARCHAR(100)'),
@empID,
GETDATE()
FROM @xmlData.nodes('/vehicles/vehicle') AS ParamValues(x2)
--Something else
Upvotes: 0
Views: 63
Reputation: 20975
Yes. All 6 Rows will be INSERTed before the code hits -- something else
line.
So if you want to capture all 6 IDs and INSERT those into some Audit Table or Foreign Key table, use the INSERTED table like so.
INSERT INTO dbo.SampleTable_Audit
(LastInsertedID, .....)
SELECT ID, ....
FROM INSERTED;
(Pseudocode, but you get the idea.)
Upvotes: 1