如何使用SQL modify函数迭代XML节点，在TextReadingName后插入新节点

下面提供两种常用的实现方式，可根据实际场景选择：

方法1：循环修改原XML（适合节点量少的场景） #

直接通过SQL Server的XML.modify方法逐节点插入，无需重构整个XML结构：

DECLARE @XML XML =
N'<Steplist>
  <Step>
    <StepId>e36a3450-1c8f-44da-b4d0-58e5bfe2a987</StepId>
    <Rank>1</Rank>
    <IsComplete>false</IsComplete>
    <TextReadingName>bug-8588_Updated3</TextReadingName>     
  </Step>
  <Step>
    <StepId>4078c1b1-71ea-4578-ba61-d2f6a5126ba1</StepId>
    <Rank>2</Rank>
    <TextReadingName>reading1</TextReadingName>
  </Step>
</Steplist>';

DECLARE @StepCount INT = @XML.value('count(/Steplist/Step)', 'INT')
DECLARE @i INT = 1

WHILE @i <= @StepCount
BEGIN
    SET @XML.modify('
        insert element TextReadingId {sql:column("RankVal")} 
        after (/Steplist/Step[sql:variable("@i")]/TextReadingName)[1]
    ')
    FROM (
        SELECT @XML.value('(/Steplist/Step[sql:variable("@i")]/Rank)[1]', 'INT') AS RankVal
    ) t
    SET @i = @i + 1
END

-- 输出修改后的结果
SELECT @XML

方法2：直接重构新XML（适合节点量大的场景） #

通过nodes方法拆分所有Step节点后重新拼接，性能优于循环修改，写法更简洁：

DECLARE @XML XML =
N'<Steplist>
  <Step>
    <StepId>e36a3450-1c8f-44da-b4d0-58e5bfe2a987</StepId>
    <Rank>1</Rank>
    <IsComplete>false</IsComplete>
    <TextReadingName>bug-8588_Updated3</TextReadingName>     
  </Step>
  <Step>
    <StepId>4078c1b1-71ea-4578-ba61-d2f6a5126ba1</StepId>
    <Rank>2</Rank>
    <TextReadingName>reading1</TextReadingName>
  </Step>
</Steplist>';

SELECT 
    StepXml.query('StepId'),
    StepXml.query('Rank'),
    StepXml.query('IsComplete'),
    StepXml.query('TextReadingName'),
    StepXml.value('(Rank)[1]', 'INT') AS TextReadingId
FROM @XML.nodes('/Steplist/Step') AS T(StepXml)
FOR XML PATH('Step'), ROOT('Steplist'), TYPE

如果Step节点下还有其他需要保留的自定义字段，在查询列中补充对应的query方法即可。

内容的提问来源于stack exchange，提问作者Helen Araya