如何通过XQuery在SQL中使用节点的当前值更改节点值(SQL Server) [英] How to change node value using node's current value in SQL with XQuery (SQL Server)
本文介绍了如何通过XQuery在SQL中使用节点的当前值更改节点值(SQL Server)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何更改:
<data>
<row>
<a>A</a>
<a>B</a>
<a>C</a>
</row>
</data>
收件人:
<data>
<row>
<a>Data A</a>
<a>Data B</a>
<a>Data C</a>
</row>
</data>
在SQL中?我见过很多关于如何用静态值完全替换值的示例,但是没有动态替换值的示例.
in SQL? I've seen lots of examples on how to completely replace a value with a static value, but no examples of replacing the value dynamically.
推荐答案
我不知道是否可以在replace value of
语句中使用xml.但是你可以做到这一点.
I do not know if it is possible to use the xml in the replace value of
statement. But you can do this.
declare @xml xml = '
<data>
<row>
<a>A</a>
<a>B</a>
<a>C</a>
</row>
</data>'
declare @Val1 varchar(10)
declare @Val2 varchar(10)
declare @Val3 varchar(10)
select
@Val1 = 'Data '+r.value('a[1]', 'varchar(1)'),
@Val2 = 'Data '+r.value('a[2]', 'varchar(1)'),
@Val3 = 'Data '+r.value('a[3]', 'varchar(1)')
from @xml.nodes('/data/row') n(r)
set @xml.modify('replace value of (/data/row/a/text())[1] with (sql:variable("@val1"))')
set @xml.modify('replace value of (/data/row/a/text())[2] with (sql:variable("@val2"))')
set @xml.modify('replace value of (/data/row/a/text())[3] with (sql:variable("@val3"))')
版本2
declare @xml xml = '
<data>
<row>
<a>A</a>
<a>B</a>
<a>C</a>
</row>
<row>
<a>1</a>
<a>2</a>
<a>3</a>
</row>
</data>'
;with cte as
(
select
r.query('.') as Row,
row_number() over(order by (select 0)) as rn
from @xml.nodes('/data/row') n(r)
)
select
(select
'Data '+a.value('.', 'varchar(1)')
from cte as c2
cross apply Row.nodes('row/a') as r(a)
where c1.rn = c2.rn
for xml path('a'), root('row'), type)
from cte as c1
group by rn
for xml path(''), root('data')
这篇关于如何通过XQuery在SQL中使用节点的当前值更改节点值(SQL Server)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文