Как обновить атрибуты XML на основе существующих значений в SQL?
У меня есть много 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. Как добавить в этот оператор какой-то переключатель для динамического вычисления шестнадцатеричного кода? Также у него нет возможности обновить ВСЕ атрибуты. Он обновляет только первый элемент
Задавая вопрос, вы должны предоставить минимальный воспроизводимый пример: (1) DDL и набор выборочных данных, т. е. таблицы CREATE плюс операторы INSERT T-SQL. (2) Что вам нужно сделать, т. е. логика и ваш код попытаются реализовать ее в T-SQL. (3) Желаемый результат, основанный на примерных данных в # 1 выше. (4) Ваша версия SQL Server (SELECT @@version;).
Ответы 1
Пожалуйста, попробуйте следующее решение.
Он создает вычисляемый столбец ColorHexValue внутри CTE.
Следующим шагом является обновление атрибута столбца XML цвет значением столбца ColorHexValue.
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;
Да. Это хорошо. Но, похоже, это не сработает, если XML имеет более одного атрибута. Только сначала будет обновляться. Пример: <para id = "19" revDate = "2022-05-04T04:00:00Z"> <emphasis> <emphasis color = "Blue">test</emphasis> </emphasis> </para>
Пожалуйста, отредактируйте исходный вопрос и предоставьте минимальный воспроизводимый пример, как в моем ответе.
Обновил вопрос
Я обновил ответ с помощью SQL №2
Есть ошибка, которую я исправил: вы написали with ( if (//emphasis/@color = "Blue"), а должно было быть with ( if (//emphasis/@color[. = ("Blue","Red","Green")] = "Blue")
Использовать таблицу поиска? Сам SQL Server понятия не имеет, каким должен быть шестнадцатеричный код «LemonChiffon» или «Gainsboro».