SQL: Update xml column in a table recursively

Question

I have a table in sql server 2012

With the following column definition

   CREATE TABLE [dbo].[tblStepList](
    [ToDoId] [int] IDENTITY(1,1) NOT NULL,
    [Data] [xml] NOT NULL
}

And the data column is xml with

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

I want to update each row of the table with my new xml to look with new node named TextReadingId after TextReading name

I want to insert a new node named TextReadingId I want my TextReadingId values to be running numbers as follows

 <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>1</TextReadingId> 
          </Step>
        </Steplist>';

This is what I tried but it is not working as expected

DECLARE @i int;

SELECT

@i = s.data.value('count(/Steplist/Step)', 'nvarchar(max)')

FROM tblStepList   s

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

print @i
End

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Oct 26 '21 at 18:28

Yitzhak Khabinsky · Accepted Answer · 2021-10-26T17:52:50.173

3

Please try the following solution.

It is using XQuery and its FLWOR expression.

SQL

USE tempdb;
GO

DROP TABLE IF EXISTS [dbo].[tblStepList];

CREATE TABLE [dbo].[tblStepList](
    [ToDoId] [int] IDENTITY(1,1) NOT NULL,
    [Data] [xml] NOT NULL
);

INSERT INTO dbo.tblStepList ([Data]) VALUES
(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>');

-- before
SELECT * FROM dbo.tblStepList;

UPDATE dbo.tblStepList
SET [Data]  = [Data].query('<Steplist>
{
    for $x in /Steplist/Step
    let $pos := count(Steplist/*[. << $x]) + 1
    return <Step>{$x/*,
        if (not($x/TextReadingId)) then <TextReadingId>{$pos}</TextReadingId>
        else ()} 
        </Step>
}
</Steplist>');

-- after
SELECT * FROM dbo.tblStepList;

edited Oct 26 '21 at 17:52

answered Oct 26 '21 at 17:43

Yitzhak Khabinsky

18,471
2
15
21

I just updated my question – Helen Araya Oct 26 '21 at 18:17
can I get the values from a secondary table by joining using FLWOR ? insted of running numbers – Helen Araya Oct 26 '21 at 18:17
So you changed the goal posts in the middle of the game. I don't see an easy way to use another table instead of the running numbers. – Yitzhak Khabinsky Oct 26 '21 at 18:34
I reverted my question to where it was and asked another question https://stackoverflow.com/questions/69728769/using-flwor-to-get-data-from-another-table – Helen Araya Oct 26 '21 at 19:16
@ Yitzhak Khabinsky Can you check https://stackoverflow.com/q/69843694/3038042 – Helen Araya Nov 04 '21 at 17:53

SQL: Update xml column in a table recursively

1 Answers1

Linked