SQL Query to parse XML and remove data and send remaining results to multiple columns

Question

Is there a way to parse XML string data from a column from a table and easily be able to remove parts of the XML string with a wildcard automatically?

Example:

Example: 

<audit><fieldid>id="John"<fieldid>id="bob</audit>  
  
  How would i be able to remove just <fieldid>id="bob?

How would i be able to remove just <fieldid>id="bob?  

If there are more values after <fieldid>id="bob, how would i be able remove those as well, but keep the </audit> in place at the end.

I have tried the following Update Statement and it did not work with a wildcard:

Update Table
Set column=Replace(Column, '<fieldid>id="bob%', </Audit>

I have tried the following but wildcards in a replace function do not work:

update table set column=replace(column,'id="bob%',

Answer 1

Assumption:

the pattern id="bob will only occur once.

This should do it. You just need to replace the variable @var with your column:

DECLARE @Var VARCHAR(MAX) = '<audit><fieldid>id="John"<fieldid>id="bob</audit>';

SELECT REPLACE(@Var, SUBSTRING(@Var, PATINDEX('%<fieldid>id="bob%', @Var), PATINDEX('%<%', SUBSTRING(@Var, PATINDEX('%<fieldid>id="bob%', @Var)+1, LEN(@Var)))), '');

RESULT:

To Answer your comment "Is it possible to have the script remove all values after bob and be replaced with ?"

DECLARE @Var VARCHAR(MAX) = '<audit><fieldid>id="John"<fieldid>id="bob<fieldid>id="John"</audit>';

SELECT REPLACE(@Var, SUBSTRING(@Var, PATINDEX('%<fieldid>id="bob%', @Var), PATINDEX('%</audit>%', SUBSTRING(@Var, PATINDEX('%<fieldid>id="bob%', @Var)+1, LEN(@Var)))), '');

RESULT: