从同一/另一个表中的另一列向 XML 列添加属性

本文介绍了从同一/另一个表中的另一列向 XML 列添加属性的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

这是我的场景:

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