如何根據SQL中的現有值更新XML屬性？

我在應用程式中有很多 XML 檔案，它們存盤在 SQL Server 資料庫中。這是一個例子：

<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>

我想用相應的十六進制顏色代碼替換所有強調和輸入元素的顏色屬性值。這就是我想要得到的：

<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>

所以我需要某種開關/外殼。注意：屬性可以有任何十六進制值（不僅僅是紅色、綠色、藍色）：

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

我現在有以下腳本：

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

如您所見，它只有一個硬編碼的 HexCode。如何在此陳述句中添加某種開關以動態計算十六進制代碼？此外，它不可能更新所有屬性。它只更新第一項

uj5u.com熱心網友回復：

請嘗試以下解決方案。

它在 CTE 內創建一個計算列ColorHexValue。

下一步是使用ColorHexValue列值更新 XML 列顏色屬性。

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<>小提琴

標籤：sql服务器 xml tsql 路径 xquery-sql

上一篇：XSLT按條件對兄弟姐妹進行分組，第2部分？

下一篇：XSLT根據條件對兄弟姐妹進行分組？