Find a string with in a TEXT type column that contains an XML file and replace a value in SQL Server-CodePudding

Update: The column that has the XML file has a data type of text.

I have a column called Settings that contains xml file with user settings.

For example I have 3 users that have the following rows for this column.

XML for the first user id 1:

<owner>
    <product userid="1" productid="3" region="North" country="Usa" ></product>
</owner>

XML for the first user id 2:

<owner>
    <product userid="2" productid="3" selectedView="true" region="North" state="AZ" country="Usa" ></product>
</owner>

XML for the first user id 3:

<owner>
    <product userid="3" productid="3" selectedView="true" region="South" isSelected="true" state="AZ" country="Usa" ></product>
</owner>

As you can see, the attributes can be in any order within the XML files for each user. I have to update all the users to have region = "East". I tried doing it using CharIndex and substring but it's getting too confusing. Any ideas on how to do this? Thanks!

CodePudding user response：

You can use the .modify() XML function for this, using the replace value of syntax

UPDATE users
SET Settings.modify('replace value of (owner/product/@region)[1] with "East"');

db<>fiddle

This only works on one XML document per row.

If you actually have all these nodes in one big blob, you need to run it in a loop

DECLARE @tries int = 0;
WHILE @@ROWCOUNT > 0 AND @tries < 1000
BEGIN
    UPDATE users
    SET Settings.modify('replace value of (owner/product/@region[. != "East"])[1] with "East"')
    WHERE Settings.exist('owner/product/@region[. != "East"]') = 1;
    SET @tries  = 1;
END;

db<>fiddle

CodePudding user response：

You could use a case statement in the select Case When colName like '%NORTH%' then replace (colName, NORTH, NEWTEXT When colName like '%SOUTH%' etc

You could also create a stored function

CodePudding user response：

Try good old REPLACE ( string_expression , string_pattern , string_replacement ) in your SQL query