SQL: Use modify function to iterate on xml nodes or How to insert or modify nodes recursively

Question

I have an xml as show in the code below

I want to iterate though each node and insert one node after each TextReadingName node

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 x.XmlCol.value('(StepId)[1]', 'nvarchar(max)') as StepId
--FROM   @xml.nodes('/Steplist/Step') x(XmlCol)

This is how I want my new xml to look

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

I am able to select nodes as shown below but not able to iterate through each node and modify the data

--SELECT x.XmlCol.value('(StepId)[1]', 'nvarchar(max)') as StepId
--FROM   @xml.nodes('/Steplist/Step') x(XmlCol)

I am looking same function as above but for inserting nodes

This is how I want my new xml to look with new node named TextReadingId after TextReading name

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

score 1 · Accepted Answer · answered Oct 24 '21 at 09:09

1

You can use modify with insert, but the main drawback seems to be that SQL server only allows insert into a single node, I couldn't combine it with a FLOWR expression, so I had to use an SQL WHILE loop to perform various modify calls:

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>';

BEGIN

DECLARE @i int;

SELECT @i = @XML.value('count(Steplist/Step)', 'int');

WHILE @i > 0
  BEGIN

    SET @XML.modify('insert <TextReadingId>{sql:variable("@i")}</TextReadingId> as last into (/Steplist/Step[sql:variable("@i")])[1]');

    SET @i = @i - 1;
  END
END
SELECT @XML;

answered Oct 24 '21 at 09:09

Martin Honnen

160,499
6
90
110

How Can I modify this to be used in each table row. When I tried it in my table it is just updating the last row. – Helen Araya Oct 26 '21 at 16:49
Ask a new question or at least edit the question with a table/column definition of the table where you need to update the XML. – Martin Honnen Oct 26 '21 at 16:51
https://stackoverflow.com/questions/69727258/sql-update-xml-column-in-a-table-recursively just asked a new one – Helen Araya Oct 26 '21 at 17:06
can you check https://stackoverflow.com/questions/69728769/using-flwor-to-get-data-from-another-table – Helen Araya Oct 26 '21 at 19:17
Honnencan you check https://stackoverflow.com/q/69843694/3038042 – Helen Araya Nov 04 '21 at 17:54

SQL: Use modify function to iterate on xml nodes or How to insert or modify nodes recursively

1 Answers1