Как обновить поле XML в SQL Server

Question

У меня есть столбец XML называется xmlValue в таблице SQL Server tbl1 с типом данных nvarchar(max),

Значение xml в этом столбце выглядит следующим образом:

<?xml version="1.0" encoding="UTF-8"?>
<main>  
    <sub>
        <subMode>
            <name>abc</name>
            <address>add abc</address>
        </subMode>
        <subMode>
            <name>xyz</name>
            <address>add xyz</address>
        </subMode>
    <sub>
</main>

В настоящее время адресным значением имени 'xyz' является 'add xyz'. Мне нужно обновить его до чего-то еще, скажем "добавить XYZ обновлен".

Есть ли простой способ сделать это.

Я попытался использовать решение, представленное в разделе Как обновить XML в SQL на основе значений в этом XML, но это кажется сложным.

У кого-нибудь есть более простое решение для достижения этой цели?

Answer 1

Вам уже говорили, что ваш XML должен храниться как нативный XML.

Ваша проблема многогранна

• неправильный тип данных (NVARCHAR(MAX) вместо XML)
• Приведение к XML из NVARCHAR не допускается с объявлением UTF-8 кодирование
• .modify не применимо на лету

Таким образом, обходной путь - временная таблица

Сценарий макета

DECLARE @tbl TABLE(ID INT IDENTITY, YourXmlAsString NVARCHAR(MAX));
INSERT INTO @tbl VALUES
('<?xml version="1.0" encoding="UTF-8"?>
<main>  
    <sub>
        <subMode>
            <name>abc</name>
            <address>add abc</address>
        </subMode>
        <subMode>
            <name>xyz</name>
            <address>add xyz</address>
        </subMode>
    </sub>
</main>');

--Это SELECT преобразует ваш string-XML и сохраняет результат как настоящий XML

SELECT ID
        ,CAST(REPLACE(YourXmlAsString,'UTF-8','UTF-16') AS XML) CastedToRealXML
        ,YourXmlAsString AS OriginalValue
INTO #tempTblKeepsCastedValue
FROM @tbl 
--WHERE SomeCriteria;

- Вы ищете xyz и добавляете что-то к существующему значению

DECLARE @SearchForName NVARCHAR(100)=N'xyz';
DECLARE @Append NVARCHAR(100)=N'add to the value';

UPDATE #tempTblKeepsCastedValue
SET CastedToRealXML.modify('replace value of 
                            (/main/sub/subMode[name/text()=sql:variable("@SearchForName")]/address/text())[1]
                            with concat((/main/sub/subMode[name/text()=sql:variable("@SearchForName")]/address/text())[1],sql:variable("@Append"))');

- Теперь вы обновляете исходную таблицу, используя INNER JOIN к временному столу

UPDATE t
SET YourXmlAsString=CAST(tmp.CastedToRealXml AS NVARCHAR(MAX))
FROM @tbl AS t
INNER JOIN #tempTblKeepsCastedValue AS tmp ON t.ID=tmp.ID;

- Результат (и очистка)

SELECT * FROM @tbl
DROP TABLE #tempTblKeepsCastedValue;

ID  YourXmlAsString
1   <main><sub><subMode><name>abc</name><address>add abc</address></subMode><subMode><name>xyz</name><address>add xyzadd to the value</address></subMode></sub></main>