Add an attribute to the XML Column from another column in the same/another table

Question

Here's my scenario:

--ORDER table
OrderID OrderCode DateShipped    ShipmentXML
1       ABC       08/06/2013     <Order><Item CustomerName="BF" City="Philadelphia" State="PA"></Item></Order>
2       XYZ       08/05/2013     <Order><Item CustomerName="TJ" City="Richmond" State="VA"></Item></Order>

At some point in the process, I will know the respective TrackingNumber for these Orders. The tracking numbers are available in another table like this:

 --TRACKING table
 TrackingID    OrderCode   TrackingNumber    
 98            ABC         1Z1               
 99            XYZ         1Z2

The output I'm expecting is as below:

   OrderID OrderCode     ShipmentXML
   1       ABC           <Order><Item CustomerName="BF" City="Philadelphia" State="PA" DateShipped="08/06/2013" TrackingNumber="1Z1"></Item></Order>
   2       XYZ           <Order><Item CustomerName="TJ" City="Richmond" State="VA" DateShipped="08/05/2013" TrackingNumber="1Z2"></Item></Order>`

As you can see, I'm trying to get the TrackingNumber and the DateShipped for each OrderCode and have them as an attribute. The intent is a SELECT, not UPDATE.

All the examples I've seen demonstrate how to update the XML with a Constant value or a variable. I couldn't find one that demonstrates XML updates with a JOIN. Please help with how this can be accomplished.

UPDATE:

By 'Select not Update', I meant that no updates to the permanent table; UPDATE on temp tables are perfectly fine, as Mikael commented below the first answer.

Answer 1

A version using a temp table to add the attributes to the XML.

select OrderID,
       OrderCode,
       DateShipped,
       ShipmentXML
into #Order
from [Order]

update #Order
set ShipmentXML.modify
  ('insert attribute DateShipped {sql:column("DateShipped")} 
    into (/Order/Item)[1]')

update O
set ShipmentXML.modify
  ('insert attribute TrackingNumber {sql:column("T.TrackingNumber")} 
    into (/Order/Item)[1]')
from #Order as O
  inner join Tracking as T
    on O.OrderCode = T.OrderCode

select OrderID,
       OrderCode,
       ShipmentXML
from #Order

drop table #Order