Unfortunately, you're pretty much stuck doing something similar to what you have when your XML is stored as text.
You can try something like this if you want to get rid of the use of temp tables. You can run this in SSMS.
/* Base table mock-up */
DECLARE @Data TABLE ( [data] NTEXT, [id] INT IDENTITY (1,1) );
INSERT INTO @Data ( [data] ) VALUES
( '<root><values><val>Value 1</val><val>to_be_changed</val></values></root>' ),
( '<root><values><val>All is well here.</val><val>All is well here, too.</val></values></root>' ),
( '<root><values><val>Another value.</val><val>to_be_changed</val></values></root>' );
/* Find/Replace variables */
DECLARE
@find_value VARCHAR(50) = 'to_be_changed',
@replace_value VARCHAR(50) = 'Value 2';
/* Create a table variable to temporarily house the ntext data as xml so the XML may be modified */
DECLARE @Temp TABLE ( DataXml XML, id INT );
/* Insert [data] into the XML column */
INSERT INTO @Temp ( DataXml, [id] )
SELECT CAST ( [data] AS XML ), [id] FROM @Data WHERE [data] LIKE '%' + @find_value + '%';
/* Show the @Data resultset before modifying */
SELECT * FROM @Data;
/* The WHILE is to make sure every node that requires updating gets updated */
/* Modify each instance matching the @find_value criteria */
WHILE EXISTS ( SELECT * FROM @Temp WHERE DataXml.exist( '//root/values/val/text()[.=sql:variable("@find_value")]' ) = 1 )
UPDATE @Temp
SET
DataXml.modify ('
replace value of (/root/values/val/text()[.=sql:variable("@find_value")])[1]
with sql:variable("@replace_value")
');
/* Update the results back to the ntext column */
UPDATE @Data
SET
[data] = CAST ( t.DataXml AS NVARCHAR(MAX) )
FROM @Data d
INNER JOIN @Temp t
ON d.id = t.id;
/* Show the updated @Data resultset */
SELECT * FROM @Data;
The initial select of @Data:
/* Show the @Data resultset before modifying */
SELECT * FROM @Data;
Returns
+---------------------------------------------------------------------------------------------+----+
| data | id |
+---------------------------------------------------------------------------------------------+----+
| <root><values><val>Value 1</val><val>to_be_changed</val></values></root> | 1 |
| <root><values><val>All is well here.</val><val>All is well here, too.</val></values></root> | 2 |
| <root><values><val>Another value.</val><val>to_be_changed</val></values></root> | 3 |
+---------------------------------------------------------------------------------------------+----+
And the final resultset of @Data:
/* Show the updated @Data resultset */
SELECT * FROM @Data;
Returns
+---------------------------------------------------------------------------------------------+----+
| data | id |
+---------------------------------------------------------------------------------------------+----+
| <root><values><val>Value 1</val><val>Value 2</val></values></root> | 1 |
| <root><values><val>All is well here.</val><val>All is well here, too.</val></values></root> | 2 |
| <root><values><val>Another value.</val><val>Value 2</val></values></root> | 3 |
+---------------------------------------------------------------------------------------------+----+
Possible alternative method:
Perhaps a simple REPLACE on your text.
UPDATE @Data
SET
[data] = REPLACE ( CAST ( [data] AS NVARCHAR(MAX) ), @find_value, @replace_value )
FROM @Data d
WHERE
d.[data] LIKE '%' + @find_value + '%';
UPDATE:
I should have been more clear by saying "I don't want to use any kind of intermediary tables"
/* For-each find/replace instance found... */
WHILE EXISTS ( SELECT * FROM @Data WHERE CAST ( [data] AS XML ).exist( '//root/values/val/text()[.=sql:variable("@find_value")]' ) = 1 )
BEGIN
DECLARE @id INT, @xml XML;
SELECT TOP 1
@id = id,
@xml = CAST ( [data] AS XML )
FROM @Data
WHERE CAST ( [data] AS XML ).exist( '//root/values/val/text()[.=sql:variable("@find_value")]' ) = 1;
-- Modify the XML --
SET @xml.modify('
replace value of (/root/values/val/text()[.=sql:variable("@find_value")])[1]
with sql:variable("@replace_value")
');
-- Update the modified XML --
UPDATE @Data
SET
[data] = CAST ( @xml AS NVARCHAR(MAX) )
WHERE id = @id;
END
/* Show the updated resultset */
SELECT * FROM @Data ORDER BY id;
Updated Resultset:
+---------------------------------------------------------------------------------------------+----+
| data | id |
+---------------------------------------------------------------------------------------------+----+
| <root><values><val>Value 1</val><val>Value 2</val><val>Value 2</val></values></root> | 1 |
| <root><values><val>All is well here.</val><val>All is well here, too.</val></values></root> | 2 |
| <root><values><val>Another value.</val><val>Value 2</val></values></root> | 3 |
+---------------------------------------------------------------------------------------------+----+
UPDATE BY OP
Thanks for your last solution, I ended up doing this, no need for a while loop
DECLARE @CURRENT_EXAM_CODE NVARCHAR(10) = 'BXC_14B'
DECLARE @NEW_EXAM_NAME NVARCHAR(10) = 'BCC'
DECLARE @CODE_DESC NVARCHAR(50)
DECLARE @XML_DATA XML
-- convert existing NTEXT data into XML
SELECT @XML_DATA =
CAST([data] as xml)
FROM [dbo].[CodeSystemCodes_data]
WHERE [data] like '%' + @CURRENT_EXAM_CODE + '%'
-- update the xml data
SET @XML_DATA.modify('replace value of (/Utilities.CodeSystems.CodeSystemCodes/@Description)[1] with sql:variable("@NEW_EXAM_NAME")')
SET @CODE_DESC = @CURRENT_EXAM_CODE + ' - ' + @NEW_EXAM_NAME
SET @XML_DATA.modify('replace value of (/Utilities.CodeSystems.CodeSystemCodes/@CodeAndDescription)[1] with sql:variable("@CODE_DESC")')
-- convert xml data back to ntext type
UPDATE [dbo].[CodeSystemCodes_data]
SET
[data] = CAST(CAST(@XML_DATA as nvarchar(max)) as ntext)
WHERE [data] like '%' + @CURRENT_EXAM_CODE + '%'
