SQL Update Multiple XML Node Values without Loop

Question

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?

Answer 1

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;

Answer 2

You could write it a little more efficiently, by looping until there is nothing to update, and using a WHERE exists filter to only update rows that need updating.

WHILE 1=1
BEGIN
    UPDATE TableName
    SET XMLColumnName.modify('
      replace value of
      (data/Table [@TableId = "Classes"] /TableRow/ClassName/text()[. = "Oak"])[1]
      with "Maple"
    ')
    WHERE XMLColumnName.exist('
      data/Table [@TableId = "Classes"] /TableRow/ClassName/text()[. = "Oak"]
    ') = 1;

    IF @@ROWCOUNT = 0
        BREAK;
END;

db<>fiddle

Or you could rebuild the XML using XQuery, but this is going to be complicated with such a deeply nested structure.

On each level, check if you have the node you want. If yes, create a replacement node (and go down another level if needed). Otherwise just return what you have.

UPDATE TableName
SET XMLColumnName = 
  XMLColumnName.query('
  <data>
  {
    for $t in data/*
    return
      if ($t/@TableId = "Classes")
      then
        <Table>
        {
          for $r in $t/*
          return
            if ($r/ClassName/text()[. = "Oak"])
            then
              <TableRow RowNumber = "{$r/@RowNumber}">
              {
                for $n in $r/*
                return
                  if (local-name($n) = "ClassName")
                  then
                   <ClassName>Maple</ClassName>
                  else $n
              }
              </TableRow>
            else $r
        }
        </Table>
      else $t
  }
  </data>
    ')
WHERE XMLColumnName.exist('
  data/Table [@TableId = "Classes"] /TableRow/ClassName/text()[. = "Oak"]
') = 1;

db<>fiddle