这是我的场景:
--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 列添加属性
发布评论