title: How to update XML attributes based on existing values in SQL?
body: <p>I have a lot of XML documents in the app which are stored in the SQL Server database. Here is an example:</p>
  <pre><code>&lt;para id=&quot;19&quot; revDate=&quot;2022-05-04T04:00:00Z&quot;&gt;
    &lt;emphasis&gt;
      &lt;emphasis color=&quot;Blue&quot;&gt;test&lt;/emphasis&gt;
      &lt;emphasis color=&quot;Red&quot;&gt;test&lt;/emphasis&gt;
    &lt;/emphasis&gt;
    &lt;emphasis&gt;
      &lt;emphasis color=&quot;Green&quot;&gt;test&lt;/emphasis&gt;
    &lt;/emphasis&gt;
  &lt;/para&gt;
  </code></pre>
  <p>I want to replace the values of <strong>color</strong> attributes for all <strong>emphasis</strong> and <strong>entry</strong> elements with the corresponding Hex color code. This is what i want to get:</p>
  <pre><code>&lt;para id=&quot;19&quot; revDate=&quot;2022-05-04T04:00:00Z&quot;&gt;
    &lt;emphasis&gt;
      &lt;emphasis color=&quot;0000FF&quot;&gt;test&lt;/emphasis&gt;
      &lt;emphasis color=&quot;FF0000&quot;&gt;test&lt;/emphasis&gt;
    &lt;/emphasis&gt;
    &lt;emphasis&gt;
      &lt;emphasis color=&quot;008000&quot;&gt;test&lt;/emphasis&gt;
    &lt;/emphasis&gt;
  &lt;/para&gt;
  </code></pre>
  <p>So i will need some kind of switch/case. Note: attribute could have any hex value (not just red, green, blue):</p>
  <pre><code>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
  </code></pre>
  <p>I have the following script right now:</p>
  <pre><code>DECLARE @tbl TABLE (XmlData XML);
  INSERT INTO @tbl VALUES
  ('&lt;para id=&quot;19&quot; revDate=&quot;2022-05-04T04:00:00Z&quot;&gt;
      &lt;emphasis&gt;
        &lt;emphasis color=&quot;Blue&quot;&gt;test&lt;/emphasis&gt;
        &lt;emphasis color=&quot;Red&quot;&gt;test&lt;/emphasis&gt;
      &lt;/emphasis&gt;
      &lt;emphasis&gt;
        &lt;emphasis color=&quot;Green&quot;&gt;test&lt;/emphasis&gt;
      &lt;/emphasis&gt;
    &lt;/para&gt;'
  );
  
  UPDATE 
    [XmlDocument]
  SET
    [XmlData].modify('replace value of (//*[self::emphasis or self::entry]/@color)[1] with &quot;hexCodeHere&quot;')
  FROM 
    @tbl AS [XmlDocument]
  WHERE 
    [XmlDocument].[XmlData].exist('//*[self::emphasis or self::entry][@color]') = 1
  
  
  SELECT * FROM @tbl
  </code></pre>
  <p>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</p>