我想向查询中的 xml 字段添加属性值。我的例子如下
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
我希望我的最终结果看起来像这样
<book title="you are not alone" author="Esther" quantity="3">
<EDITIONS>
<edition year="2012"/>
<edition year="2013"/>
</EDITIONS>
</book>
如果需要选择数据,可以使用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 fiddle demo http://sqlfiddle.com/#!3/d41d8/21875
甚至更简单:
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 fiddle demo http://sqlfiddle.com/#!3/d41d8/21883
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)