Moving nodes in XML and rename them using XML DML

Question

Given the following XML all nodes named IsEurozone have to be renamed to HasFxrEuro.

<?xml version="1.0" encoding="utf-8"?>
<MacroScenarios>
  <MacroScenario>
    <Id>1</Id>
    <Name>Macro Scenario 1</Name>
    <Scenarios>
      <Scenario>
        <CountryIsoAlpha3>USA</CountryIsoAlpha3>
        <Id>1</Id>
        <IsEurozone>true</IsEurozone>
      </Scenario>
      <Scenario>
        <CountryIsoAlpha3>GER</CountryIsoAlpha3>
        <Id>2</Id>
        <IsEurozone>true</IsEurozone>
      </Scenario>
    </Scenarios>
  </MacroScenario>
</MacroScenarios>

The replace-operation can easily done (thanks to Mikael Erikssons post How to rename XML node name in a SQL Server):

UPDATE [dbo].[MacroScenarioSets] set
[ContentAsXml] = 
        replace(
            replace(
                cast(ContentAsXml as nvarchar(max)), 
                '<IsEurozone>', 
                '<HasFxrEuro>'),
            '</IsEurozone>', 
            '</HasFxrEuro>')

After that the content of the Scenario node would look like:

  <Scenario>
    <CountryIsoAlpha3>USA</CountryIsoAlpha3>
    <Id>1</Id>
    <HasFxrEuro>true</HasFxrEuro>
  </Scenario>

But in my case I need a strict alphabetical order of the nodes and so HasFxrEuro must be placed before Id.

My idea was to do something like

UPDATE [dbo].[MacroScenarioSets]
SET ContentAsXml.modify('insert 
<HasFxrEuro>{(/MacroScenarios/MacroScenario/Scenarios/Scenario/IsEurozone/text())}</HasFxrEuro>
after (/MacroScenarios/MacroScenario/Scenarios/Scenario/CountryIsoAlpha3)')

and after that delete all old IsEurozone nodes. But XML DML requires a singleton for insert-statements. Is there another way to achieve that with XML DML?

Answer 1

Ok finally I got it running by manually walking through the XML document:

    declare @scenarioCursor int = 1
    declare @macroScenarioCursor int = 1

    while 1 = 1
    begin

            -- Add HasFxrEuro with the value of IsEurozone
           UPDATE @myxml
           SET content.modify('
               insert 
                <HasFxrEuro>{(/MacroScenarios/MacroScenario[sql:variable("@macroScenarioCursor")]/Scenarios/Scenario[sql:variable("@scenarioCursor")]/IsEurozone[1]/text())}</HasFxrEuro>
               after ((/MacroScenarios/MacroScenario[sql:variable("@macroScenarioCursor")]/Scenarios/Scenario[sql:variable("@scenarioCursor")]/CountryIsoAlpha3))[1]')
           WHERE content.exist('
                (/MacroScenarioSet/MacroScenarios/MacroScenario[sql:variable("@macroScenarioCursor")]/Scenarios/Scenario[sql:variable("@scenarioCursor")])') = 1

           if @@ROWCOUNT = 0 
           begin
                -- the end
                if @scenarioCursor = 1
                    break;
                else
                    -- increment cursor over MacroScenario, reset cursor over Scenario
                   begin
                         SET @macroScenarioCursor = @macroScenarioCursor + 1
                         SET @scenarioCursor = 1
                   end 
            end       
            else
                begin

                    -- HasFxrEuro has been added remove IsEurozone
                    UPDATE @myxml
                    SET content.modify('
                        delete (/MacroScenarios/MacroScenario[sql:variable("@macroScenarioCursor")]/Scenarios/Scenario[sql:variable("@scenarioCursor")]/IsEurozone)')

                    -- increment Scenario cursor
                    SET @scenarioCursor = @scenarioCursor + 1
               end

end

The solution is very slow. But I have no other idea how to solve this in SQL.