22,209
社区成员
发帖
与我相关
我的任务
分享
declare @xml xml
set @xml = '<root><row><item1>1</item1><item2>2</item2></row><row><item1>3</item1><item2>4</item2></row></root>'
declare @temp xml = ''
set @temp =
(
select
'right' 'item1/@aligh'
, isnull(A.item1,'') as item1
, isnull(A.item2,'') as item2
from
(
select
A.value('(item1/text())[1]', 'VARCHAR(MAX)') AS item1
, A.value('(item2/text())[1]', 'VARCHAR(MAX)') AS item2
FROM @xml.nodes('/root/row') AS R(A)
) A
For Xml Path('row')
)
declare @xml2 xml = '<root />'
SET @xml2.modify( 'insert sql:variable("@temp") into (/root)[1]')
select @xml2