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

Use a lookup table? SQL Server, itself, has no idea what the hex code of "LemonChiffon" or "Gainsboro" should be. — Thom A
– Thom A ♦, Commented May 5, 2022 at 14:03
While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented May 5, 2022 at 14:28

Yitzhak Khabinsky · Accepted Answer · 2022-05-06 04:21:48Z

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

edited May 6, 2022 at 4:21

answered May 5, 2022 at 14:44

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Denis Kaminsky Over a year ago

Yes. This is good. But it seems it will not work when XML has more than one attribute. Only first will be updated. Example: <para id="19" revDate="2022-05-04T04:00:00Z"> <emphasis> <emphasis color="Blue">test</emphasis> </emphasis> </para>

Yitzhak Khabinsky Over a year ago

Please edit your original question, and provide a minimal reproducible example like in my answer.

Yitzhak Khabinsky Over a year ago

I updated the answer with SQL #2

Charlieface Over a year ago

There is a bug which I fixed: you wrote with ( if (//emphasis/@color = "Blue") and should have been with ( if (//emphasis/@color[. = ("Blue","Red","Green")] = "Blue")

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related