从同一/另一个表中的另一列向 XML 列添加属性 [英] Add an attribute to the XML Column from another column in the same/another table

问题描述

这是我的场景:

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

我期待的输出如下:

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

如您所见，我正在尝试获取每个 OrderCode 的 TrackingNumber 和 DateShipped 并将它们作为属性.意图是选择，而不是更新.

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.

我见过的所有示例都演示了如何使用常量值或变量更新 XML.我找不到用 JOIN 演示 XML 更新的方法.请帮助说明如何实现这一点.

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.

更新:

选择不更新"是指不更新永久表；更新临时表非常好，正如 Mikael 在第一个答案下面评论的那样.

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.

推荐答案

使用临时表将属性添加到 XML 的版本.

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

这篇关于从同一/另一个表中的另一列向 XML 列添加属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！