Update XML node value based on a match from XML type column

Question

I have a SQL table with a XML column.

Table structure is

CREATE TABLE [dbo].[UserSettings](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [int] NOT NULL,    
    [UserName] [varchar](100) NULL, 
    [Setting] [xml] NULL,   
    [CreateDate] [datetime] NOT NULL
)

The Setting xml column looks like this:

<UserSettings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <UserID>1</UserID>
  <Reports>
        <string>Report - 1</string>
        <string>Report - 2</string>
        <string>Report - 3</string>
        <string>Report - 4</string>
  </Reports>
</UserSettings>

I want to update Report - 1 to Report - One, Report - 2 to Report - Two in <Reports>

And also, if there is a value Report - 4 then I want to remove it from <Reports>

So my end result for in the column should look like this:

<UserSettings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <UserID>1</UserID>
  <Reports>
        <string>Report - One</string>
        <string>Report - Two</string>
        <string>Report - Three</string>     
  </Reports>
</UserSettings>

EDIT: I have tried the REPLACE approach. I am not sure if that is the most effective way to do this.

update UserSettings
set Setting=REPLACE(convert(varchar(max),Setting),'<string>Report - 1</string>','<string>Report - One</string>')
where convert(varchar(max),Setting) like '%<string>Report - 1</string>%'

EDIT: I read about modify() method but the sequence of report could be in any order. I couldn't get my query to work with it.

What have you tried? Where did you get stuck?

Dale K
– Dale K

2020-11-06 00:03:54 +00:00
Commented Nov 6, 2020 at 0:03 — Dale K
– Dale K, Commented Nov 6, 2020 at 0:03
Have you read the modify() method documentation yet?

AlwaysLearning
– AlwaysLearning

2020-11-06 03:05:32 +00:00
Commented Nov 6, 2020 at 3:05 — AlwaysLearning
– AlwaysLearning, Commented Nov 6, 2020 at 3:05

eshirvana · Accepted Answer · 2020-11-06 03:19:50Z

1

simple way is to use Replace as string, however this method is not useful if you don't want to replace that value everywhere ( for example outside of <Reports> tag)

-- to update report 1 to 3 ( I just did it for 1):
update UserSettings
set Setting = replace(cast(Setting as nvarchar(max)) , '<string>Report - 1</string>','<string>Report - one</string>')

-- to remove report 4 
update UserSettings
set Setting = replace(cast(Setting as nvarchar(max)) , '<string>Report - 4</string>','')

answered Nov 6, 2020 at 3:19

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yitzhak Khabinsky · Accepted Answer · 2020-11-06 04:22:53Z

You can try a different approach.

Instead of find-and-replace, i.e.modify, it is possible to compose XML on the fly via XQuery and its FLWOR expression.

SQL

-- DDL and sample data population, start
DECLARE @UserSettings TABLE (
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [int] NOT NULL,    
    [UserName] [varchar](100) NULL, 
    [Setting] [xml] NULL,   
    [CreateDate] [datetime] NOT NULL
);
INSERT INTO @UserSettings (UserID, UserName, Setting, CreateDate)
VALUES
(0, 'John', N'<UserSettings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
              xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <UserID>1</UserID>
    <Reports>
        <string>Report - 1</string>
        <string>Report - 2</string>
        <string>Report - 3</string>
        <string>Report - 4</string>
    </Reports>
</UserSettings>', 
    GETDATE()
);
-- DDL and sample data population, end

UPDATE @UserSettings
SET Setting = Setting.query('<UserSettings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
              xmlns:xsd="http://www.w3.org/2001/XMLSchema">
{
    for $x in /UserSettings/*[local-name()!="Reports"]
    return $x,
        element Reports {
            for $y in ("One","Two","Three")
            return <string>{concat("Report - ", $y)}</string>
        }
}
</UserSettings>');

-- test
SELECT * FROM @UserSettings;

Collectives™ on Stack Overflow

Update XML node value based on a match from XML type column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related