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

Question

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

---

Update 2 (This helped me use xml functions instead of using charindex to manipulate the data): I created a temp table #tmpXML (id int, xmlField XML). id is the Primary key of my main table and xmlField is the XML value from the text type field. I used XML functions on the XML data field in the temp table and got all the values I needed and I updated my main table with these values.

I found this method to be more efficient and clean than using sql server functions like charindex and substring. This is the reason why I accepted the answer because it helped me use xml functions. Just wanted to clarify.

---

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!

Answer 1

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

Answer 2

Please try the following solution.

Text is a deprecated data type in the MS SQL Server. I used NVARCHAR(MAX) data type to distinguish it from the natural XML data type for the XML data.

SQL

DECLARE @tbl TABLE (id int primary key, Settings NVARCHAR(MAX));
INSERT @tbl VALUES
(1,'<owner>
    <product userid="1" productid="3" region="North" country="Usa" ></product>
</owner>'),
(2,'<owner>
    <product userid="2" productid="3" selectedView="true" region="North" state="AZ" country="Usa" ></product>
</owner>'),
(3,'<owner>
    <product userid="3" productid="3" selectedView="true" region="South" isSelected="true" state="AZ" country="Usa" ></product>
</owner>');

WITH rs AS
(
    SELECT * 
    FROM @tbl
        CROSS APPLY (SELECT TRY_CAST(Settings AS XML)
            .query('<owner><product>
                {
                    for $x in /owner/product/@*
                    return if(local-name($x) ne "region") then $x
                        else attribute region {"East"}
                }
            </product></owner>')) t(x)
)
UPDATE rs
SET rs.Settings  = TRY_CAST(x AS NVARCHAR(MAX));

-- test
SELECT * FROM @tbl;