T-SQL: scope_identity() misassigned on xml node parsing

[IKE]

Techies--I'm shredding through an Xml object without any issues on picking up the correct elements for the correct nodes, however I need to properly update a detail identifier, which is holding on to a single value, when what I really need it to do is refresh that value with the value it just wrote. I like the efficiency of the current shredding process--soI don't want to lose that by coming up with a foolish solution. Please advise me on the best approach on how to fix this logic. Here's what's happening:\[code\] -- Given: -- @OrderXml is an incoming, populated order containg the header, -- subheaders and detail records -- This logic succeeds -- step #1: write the header row in the header table insert into [order].OrderHeader ( col.cone, col.ctwo) select Order.detail.value('(cone/text())[1]','varchar(2)'), Order.detail.value('(ctwo/text()) [1]','varchar(2)') from @OrderXml.nodes('/Order') as Order(detail) select @OrderId = scope_identity() -- This logic succeeds -- step #2: write the subheader rows in the subheader table insert into [order].OrderSubHeader(col.OrderId, col.xone, col.xtwo)select@OrderId, -- this works, because no matter how many subheader rows -- get generated, the same order id needs to be associated with it.OrderSub.detail.value('(xone/text())[1]','varchar(2)'),OrderSub.detail.value('(xtwo/text()) [1]','varchar(2)')from @OrderXml.nodes('/Order/SubHeader'') as OrderSub(detail)SELECT @OrderSubId = SCOPE_IDENTITY()-- This logic FAILS-- step #3: write the detail rows in the detail tableinsert into [order].OrderDetail(col.OrderId, col.OrderSubId, col.yone, col.ytwo) select @OrderId, -- this is correct @OrderSubId, -- this is WRONG when there are multiples OrderDet.detail.value('(yone/text())[1]','varchar(2)'), OrderDet.detail.value('(ytwo/text()) [1]','varchar(2)') from @OrderXml.nodes('/Order/SubHeader/Detail'') as OrderDet(detail)\[/code\]