How to update XML attributes based on existing values in SQL?

Question

I have a lot of XML documents in the app which are stored in the SQL Server database. Here is an example:

<para id="19" revDate="2022-05-04T04:00:00Z">
  <emphasis>
    <emphasis color="Blue">test</emphasis>
    <emphasis color="Red">test</emphasis>
  </emphasis>
  <emphasis>
    <emphasis color="Green">test</emphasis>
  </emphasis>
</para>

I want to replace the values of color attributes for all emphasis and entry elements with the corresponding Hex color code. This is what i want to get:

<para id="19" revDate="2022-05-04T04:00:00Z">
  <emphasis>
    <emphasis color="0000FF">test</emphasis>
    <emphasis color="FF0000">test</emphasis>
  </emphasis>
  <emphasis>
    <emphasis color="008000">test</emphasis>
  </emphasis>
</para>

So i will need some kind of switch/case. Note: attribute could have any hex value (not just red, green, blue):

DECLARE @ColorTextValue VARCHAR(20) = 'Blue'
DECLARE @ColorHexValue VARCHAR(6)
SET @ColorHexValue = CASE @ColorTextValue
    WHEN 'Blue' THEN '0000FF'           
    WHEN 'Red' THEN 'FF0000'
    WHEN 'Green' THEN '008000'
END

I have the following script right now:

DECLARE @tbl TABLE (XmlData XML);
INSERT INTO @tbl VALUES
('<para id="19" revDate="2022-05-04T04:00:00Z">
    <emphasis>
      <emphasis color="Blue">test</emphasis>
      <emphasis color="Red">test</emphasis>
    </emphasis>
    <emphasis>
      <emphasis color="Green">test</emphasis>
    </emphasis>
  </para>'
);

UPDATE 
  [XmlDocument]
SET
  [XmlData].modify('replace value of (//*[self::emphasis or self::entry]/@color)[1] with "hexCodeHere"')
FROM 
  @tbl AS [XmlDocument]
WHERE 
  [XmlDocument].[XmlData].exist('//*[self::emphasis or self::entry][@color]') = 1


SELECT * FROM @tbl

As you can see it just has a hardcoded HexCode. How to add some kind of switch into this statement to calculate Hex code dynamically? Also it doesn't have a possibility to update ALL attributes. It updates only the first item

Answer 1

Please try the following solution.

It creates a computed column ColorHexValue inside the CTE.

Next step is to update the XML column color attribute with the ColorHexValue column value.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<para id="19" revDate="2022-05-04T04:00:00Z">
  <emphasis>
    <emphasis color="Blue">test</emphasis>
  </emphasis>
</para>'),
(N'<para id="19" revDate="2022-05-04T04:00:00Z">
  <emphasis>
    <emphasis color="Green">test</emphasis>
  </emphasis>
</para>');
-- DDL and sample data population, end

-- just to see
SELECT * 
FROM @tbl
CROSS APPLY (SELECT CASE xmldata.value('(/para/emphasis/emphasis/@color)[1]', 'VARCHAR(10)')
    WHEN 'Blue' THEN '0000ff'           
    WHEN 'Red' THEN 'ff0000'
    WHEN 'Green' THEN '008000'
END) t(ColorHexValue);

-- real deal
;WITH rs AS
(
    SELECT * 
    FROM @tbl
    CROSS APPLY (SELECT CASE xmldata.value('(/para/emphasis/emphasis/@color)[1]', 'VARCHAR(10)')
        WHEN 'Blue' THEN '0000ff'           
        WHEN 'Red' THEN 'ff0000'
        WHEN 'Green' THEN '008000'
    END) t(ColorHexValue)
)
UPDATE rs 
SET xmldata.modify('replace value of (/para/emphasis/emphasis/@color)[1] with sql:column("ColorHexValue")');

-- test
SELECT * FROM @tbl;

SQL #2

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<para id="19" revDate="2022-05-04T04:00:00Z">
    <emphasis>
      <emphasis color="Blue">test</emphasis>
      <emphasis color="Red">test</emphasis>
    </emphasis>
    <emphasis>
      <emphasis color="Green">test</emphasis>
    </emphasis>
  </para>'),
(N'<para id="19" revDate="2022-05-04T04:00:00Z">
  <emphasis>
    <emphasis color="Green">test</emphasis>
  </emphasis>
</para>');
-- DDL and sample data population, end

-- before
SELECT * FROM @tbl
WHERE xmldata.exist('//emphasis/@color[. = ("Blue","Red","Green")]') = 1;

DECLARE @tries INT = 0;

WHILE @@ROWCOUNT > 0 AND @tries < 100
BEGIN
UPDATE @tbl
SET xmldata.modify('replace value of (/para/emphasis/emphasis/@color[. = ("Blue","Red","Green")])[1]
    with (
        let $c := (/para/emphasis/emphasis/@color[. = ("Blue","Red","Green")])[1]
        return
        if ($c = "Blue") then "0000ff"
        else if ($c = "Red") then "ff0000"
        else if ($c = "Green") then "008000"
        else ("unknown color")
    )')
WHERE xmldata.exist('/para/emphasis/emphasis/@color[. = ("Blue","Red","Green")]') = 1;

    SET @tries += 1;
END;

-- after
SELECT * FROM @tbl;

db<>fiddle