title: Add an attribute to the XML Column from another column in the same/another table
body: <p>Here's my scenario: </p>
  
  <pre><code>--ORDER table
  OrderID OrderCode DateShipped    ShipmentXML
  1       ABC       08/06/2013     &lt;Order&gt;&lt;Item CustomerName="BF" City="Philadelphia" State="PA"&gt;&lt;/Item&gt;&lt;/Order&gt;
  2       XYZ       08/05/2013     &lt;Order&gt;&lt;Item CustomerName="TJ" City="Richmond" State="VA"&gt;&lt;/Item&gt;&lt;/Order&gt;
  </code></pre>
  
  <p>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: </p>
  
  <pre><code> --TRACKING table
   TrackingID    OrderCode   TrackingNumber    
   98            ABC         1Z1               
   99            XYZ         1Z2
  </code></pre>
  
  <p>The output I'm expecting is as below:</p>
  
  <pre><code>   OrderID OrderCode     ShipmentXML
     1       ABC           &lt;Order&gt;&lt;Item CustomerName="BF" City="Philadelphia" State="PA" DateShipped="08/06/2013" TrackingNumber="1Z1"&gt;&lt;/Item&gt;&lt;/Order&gt;
     2       XYZ           &lt;Order&gt;&lt;Item CustomerName="TJ" City="Richmond" State="VA" DateShipped="08/05/2013" TrackingNumber="1Z2"&gt;&lt;/Item&gt;&lt;/Order&gt;`
  </code></pre>
  
  <p>As you can see, I'm trying to get the <code>TrackingNumber</code> and the <code>DateShipped</code> for each <code>OrderCode</code> and have them as an attribute. The intent is a SELECT, not UPDATE. </p>
  
  <p>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. </p>
  
  <p>UPDATE:</p>
  
  <p>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.</p>