将列值作为属性添加到 xml 字段 [英] add column value to an xml field as an attribute

问题描述

我想向查询中的 xml 字段添加属性值.我的例子如下

I would like to add an attribute value to an xml field in a query. my example is below

declare @table table (bookid int,xmlCol xml)
insert into @table
select 1,
'<book title="you are not alone" author="Esther">
  <EDITIONS>
    <edition year="2012"/>
    <edition year="2013"/>
  </EDITIONS>
</book>'

declare @table1 table(bookid int,quantity int)
insert into @table1
select 1,3 

select ??? 
from @table t
inner join @table1 t1
on t.bookid = t1.bookid

我希望我的最终结果看起来像这样

I want my final result to look like this

<book title="you are not alone" author="Esther" quantity="3">
  <EDITIONS>
    <edition year="2012"/>
    <edition year="2013"/>
  </EDITIONS>
</book>

推荐答案

如果需要选择数据，可以使用xquery:

IF you need select data, you can use xquery:

select
    t.xmlCol.query('
         element book {
             for $i in book/@* return $i,
             attribute quantity {sql:column("t1.quantity")},
             for $i in book/* return $i
         }
    ')
from @table t
    inner join @table1 t1 on t.bookid = t1.bookid

sql 小提琴演示

甚至更简单:

select
    t.xmlCol.query('
         element book {
             book/@*,
             attribute quantity {sql:column("t1.quantity")},
             book/*
         }
    ')
from @table t
    inner join @table1 t1 on t.bookid = t1.bookid

sql 小提琴演示

这篇关于将列值作为属性添加到 xml 字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！