循环并修改XML节点SQL Server

Question

我想通过sql Server中的XML节点循环，并创建一个包含'，'的节点的副本。

例如，对于以下XML：

 declare @answerXML xml = '<answers><answer part="1">answer,test0</answer><answer part="1">answer,test1</answer></answers>'

我想被修改为以下内容：

declare @answerXML xml = '<answers><answer part="1">answer,test0</answer><answer part="1">answer, test0</answer><answer part="1">answer,test1</answer><answer part="1">answer, test1</answer></answers>'

（每个节点都是重复的，在添加的节点中，在逗号之后添加了 space 。

）。

我正在策划使用这样的东西：

SELECT 
    T.ref.value('.', 'varchar(256)') AS Answer 
FROM 
    (SELECT 
         [Xml] = @answerXML.query('for $i in data(/answers/answer) 
                                   return element temp { $i }')
    ) A
CROSS APPLY
    A.Xml.nodes('/temp') T(ref) 

但没有用，看来它太复杂了。

任何人都可以帮助如何在T-SQL中循环和更新XML吗？

预先感谢您，

Answer 1

您可以尝试将XML切碎并从头开始重建：

declare @answerXML xml=
'<answers>
  <answer part="1">answer,test0</answer>
  <answer part="1">answer,test1</answer>
</answers>';
SELECT a.value(N'@part',N'int') AS [answer/@part]
      ,a.value(N'text()[1]',N'nvarchar(max)') AS [answer] 
      ,''
      ,a.value(N'@part',N'int') AS [answer/@part]
      ,REPLACE(a.value(N'text()[1]',N'nvarchar(max)'),',',', ') AS [answer] 
FROM @answerXML.nodes(N'/answers/answer') AS A(a)
FOR XML PATH(''),ROOT('answers')

结果

<answers>
  <answer part="1">answer,test0</answer>
  <answer part="1">answer, test0</answer>
  <answer part="1">answer,test1</answer>
  <answer part="1">answer, test1</answer>
</answers>