I have a temp table with a column titled AdditionalData and for e.g. each cell contains a string (of varying length) similar to this:

<AdditionalData><Field22101>Zachary, LA</Field22101><NetMiles>23</NetMiles><MileageRate>.585</MileageRate><Field44444>Customer Related - 04</Field44444></AdditionalData>

I would like to replace everything in angle brackets by an empty space. So the output should be something like this:

Zachary, LA 23 .585 Customer Related - 04

This is my first attempt:

UPDATE TempTable

SET tAddlData = STUFF(AdditionalData, PATINDEX('<%>', AdditionalData), CHARINDEX('>', AdditionalData), '')

Obviously this only takes care of the first set of angle brackets. I know I have to use a WHILE LOOP in there but unsure how to proceed further. Can you please advise?

CodePudding user response：

A minimal reproducible example is not provided. So, I am shooting from the hip.

I am assuming that the AdditionalData column has XML data type.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, AdditionalData XML);
INSERT @tbl (AdditionalData) VALUES
(N'<AdditionalData>
    <Field22101>Zachary, LA</Field22101>
    <NetMiles>23</NetMiles>
    <MileageRate>.585</MileageRate>
    <Field44444>Customer Related - 04</Field44444>
</AdditionalData>');
-- DDL and sample data population, end

SELECT ID 
, result = AdditionalData.query('data(/*/*/text())').value('text()[1]', 'VARCHAR(MAX)')
FROM @tbl;

Output

CodePudding user response：

The other post SQL - Remove all HTML tags in a string linked by Dale K actually solved my problem. Thank you.

Solution:

UPDATE TempTable

SET tAddlData = CAST(AdditionalData AS XML).value('.', 'nvarchar(max)')