Добавить атрибут в столбец XML из другого столбца той же / другой таблицы

Question

Вот мой сценарий:

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

В какой-то момент процесса я буду знать соответствующий номер отслеживания для этих заказов. Номера отслеживания доступны в другой таблице, например:

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

Результат, который я ожидаю, такой:

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

Как видите, я пытаюсь получить TrackingNumber и DateShipped для каждого OrderCode и иметь их в качестве атрибута. Намерение - это ВЫБОР, а не ОБНОВЛЕНИЕ.

Все примеры, которые я видел, демонстрируют, как обновить XML с помощью значения Constant или переменной. Я не смог найти тот, который демонстрирует обновления XML с JOIN. Пожалуйста, помогите с тем, как это может быть достигнуто.

ОБНОВИТЬ:

Под "Выбрать не обновить" я имел в виду отсутствие обновлений для постоянной таблицы; ОБНОВЛЕНИЕ временных таблиц прекрасно, как прокомментировал Микаэль ниже первого ответа.

Answer 1

Версия, использующая временную таблицу для добавления атрибутов в 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

Answer 2

Предварительный ответ - это хорошо, но вы должны явно указать столбцы и привести их к varchar, и это не годится для будущей поддержки (если вы добавите атрибуты в ShipmentXML, вам придется изменить запрос).
Вместо этого вы можете использовать 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

или даже так:

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

см. sqlfiddle с примерами

Answer 3

Единственный известный мне способ разрешить частичное изменение данных в столбцах xml тип использует modify метод, но как указано в документации

Метод modify() типа данных xml можно использовать только в предложении SET инструкции UPDATE.

поскольку UPDATE не желателен, в качестве обходного пути я вижу измельчение и сборку вручную, как:

select
    o.OrderID,
    o.OrderCode,
    (
        cast((select
            t.c.value('@CustomerName', 'varchar(50)') as '@CustomerName',
            t.c.value('@City', 'varchar(50)') as '@City',
            t.c.value('@State', 'varchar(50)') as '@State',
            o.DateShipped as '@DateShipped',
            tr.TrackingNumber as '@TrackingNumber'
        for xml path('Item'), root('Order')) as xml)
    ) as ShipmentXML
from
    [ORDER] o
    join [TRACKING] tr on tr.OrderCode = o.OrderCode
    cross apply o.ShipmentXML.nodes('Order/Item') t(c)

Возможно, вам придется применить форматирование к o.DateShipped,