SQL Update Multiple XML Node Values without Loop

I have the following simplified XML stored in an XML Column in SQL:

<data>
  <SchoolName>My First School</SchoolName>
  <Table TableId="Classes">
    <TableRow RowNumber="1">
        <ClassName>Oak</ClassName>
        <TeacherName>Jane Smith</TeacherName>
    </TableRow>
    <TableRow RowNumber="2">
        <ClassName>Oak</ClassName>
        <TeacherName>Fred Green</TeacherName>
    </TableRow>
    <TableRow RowNumber="3">
        <ClassName>Oak</ClassName>
        <TeacherName>Mary Peters</TeacherName>
    </TableRow>
  </Table>
  <Table TableId="Pupils">
    <TableRow RowNumber="1">
      <PupilName>David</PupilName>
    </TableRow>
    <TableRow RowNumber="2">
        <PupilName>Paul</PupilName>
    </TableRow>
    <TableRow RowNumber="3">
       <PupilName>Sam</PupilName>
    </TableRow>
  </Table>
</data>

I am looking to update the "ClassName" value from "Oak" to "Maple" for all TableRows in the "Classes" XML table via a single script.

I have written the below, which works, but is time consuming over a large number of rows.

DECLARE @COUNT INT
SET @COUNT = 3 --maximum number of rows

WHILE @COUNT > 0
BEGIN
PRINT CAST(@COUNT AS NVARCHAR(30))

    UPDATE TableName    
    SET XMLColumnName.modify('replace value of (/data/Table[@TableId=("Classes")]/TableRow[@RowNumber=sql:variable("@COUNT")]/ClassName/text())[1] with "Maple"')
    WHERE ...
--move to the next table row
    SET @COUNT = @COUNT - 1
    
END

Unfortunately the initial ClassValue is not the same, so I can't do a find and replace of Oak, unless there is the option to use a wildcard e.g. % ?

Is there a more efficient way to achieve this please?

Solution

Unfortunately, SQL Server XQuery doesn't support XQuery Update Facility 3.0

Unfortunately the initial ClassValue is not the same, so I can't do a find and replace of Oak, unless there is the option to use a wildcard e.g. % ?

Please try the following method.

It is possible to specify multiple values to be replaced via XPath predicate:

[text()=("Oak","Birch")]

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xml_data XML);
INSERT @tbl (xml_data) VALUES
(N'<data>
    <SchoolName>My First School</SchoolName>
    <Table TableId="Classes">
        <TableRow RowNumber="1">
            <ClassName>Oak</ClassName>
            <TeacherName>Jane Smith</TeacherName>
        </TableRow>
        <TableRow RowNumber="2">
            <ClassName>Oak</ClassName>
            <TeacherName>Fred Green</TeacherName>
        </TableRow>
        <TableRow RowNumber="3">
            <ClassName>Oak</ClassName>
            <TeacherName>Mary Peters</TeacherName>
        </TableRow>
    </Table>
    <Table TableId="Pupils">
        <TableRow RowNumber="1">
            <PupilName>David</PupilName>
        </TableRow>
        <TableRow RowNumber="2">
            <PupilName>Paul</PupilName>
        </TableRow>
        <TableRow RowNumber="3">
            <PupilName>Sam</PupilName>
        </TableRow>
    </Table>
</data>');
-- DDL and sample data population, end

-- before
SELECT * FROM @tbl;

DECLARE @ClassName VARCHAR(10) = 'Maple';
DECLARE @UPDATE_STATUS BIT = 1;
    
WHILE @UPDATE_STATUS > 0 
BEGIN
   UPDATE t
   SET xml_data.modify('replace value of 
      (/data/Table[@TableId="Classes"]/TableRow/ClassName[text()=("Oak","Birch")]/text())[1] 
      with sql:variable("@ClassName")')
   FROM @tbl AS t
   WHERE xml_data.exist('/data/Table[@TableId="Classes"]/TableRow/ClassName[text()=("Oak","Birch")]/text()')=1;
        
   SET @UPDATE_STATUS = @@ROWCOUNT;
   PRINT @UPDATE_STATUS;
END;

-- after
SELECT * FROM @tbl;