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

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.

Answers


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

Prevous answer is good, but you have to explicitly specify columns and cast them into varchar, and that's not good for future support (if you add attributes to ShipmentXML you'll have to modify the query). Instead, you could use XQuery:

select
    O.OrderID, O.OrderCode,
    (
        select
            (select O.DateShipped, T.TrackingNumber for xml raw('Item'), type),
            O.ShipmentXML.query('Order/*')
        for xml path(''), type
    ).query('<Order><Item>{for $i in Item/@* return $i}</Item></Order>')
from [ORDER] as O
    left outer join [TRACKING] as T on T.OrderCode = O.OrderCode

or even like this:

select
    O.OrderID, O.OrderCode,
    O.ShipmentXML.query('
            element Order {
                element Item {
                    attribute DateShipped {sql:column("O.DateShipped")},
                    attribute TrackingNumber {sql:column("T.TrackingNumber")},
                    for $i in Order/Item/@* return $i
                }
            }')
from [ORDER] as O
    left outer join [TRACKING] as T on T.OrderCode = O.OrderCode

see sqlfiddle with examples


Need Your Help

UITextField: textFieldShouldBeginEditing fires, but keyboard does not show

ios6 uitextfield uitextfielddelegate

I have a text field that is apparently successfully calling delegate methods as the following method fires when I tap on the test field, but no keyboard shows.

stringtotime from Unix timestamp

php date strtotime

I am doing the following in my php script and I am not seeing the results I though I would:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.