CODEWITHSUNDEEP
sqlsql-serverxmlt-sql
SBB
SBB

Reputation: 8990

TSQL XML Parsing / Insert

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

Answers (1)

Shiva
Shiva

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

Related Questions