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

Question

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--so I 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:

  -- 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 table

insert 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)

Answer 1

Check out the OUTPUT Clause. You can capture inserted.OrderSubID in addition to any other values you want to insert into the OrderDetail table while you're inserting into OrderSubHeader. Then use that captured data (in a temp table or table variable) to insert into OrderDetail.