XML MODIFY: примеры (SQL)

Ключевые примеры использования

Ниже приведены базовые примеры для каждой инструкции DML.

1. Вставка узла (insert)

DECLARE @x xml = '<root><child>data</child></root>';
SET @x.modify('insert <newChild>new data</newChild> as first into (/root)[1]');
SELECT @x;

<root><newChild>new data</newChild><child>data</child></root>

2. Удаление узла (delete)

DECLARE @x xml = '<root><child>data</child><child2>remove me</child2></root>';
SET @x.modify('delete (/root/child2)[1]');
SELECT @x;

<root><child>data</child></root>

3. Обновление значения (replace value of)

DECLARE @x xml = '<root><child attr="old">text</child></root>';
-- Обновление атрибута
SET @x.modify('replace value of (/root/child/@attr)[1] with "new"');
SELECT @x;

<root><child attr="new">text</child></root>

-- Обновление текстового узла
DECLARE @y xml = '<root><child>old text</child></root>';
SET @y.modify('replace value of (/root/child/text())[1] with "new text"');
SELECT @y;

<root><child>new text</child></root>

Похожие функции MS SQL

Для работы с XML в MS SQL Server существуют другие методы и функции, которые решают иные задачи.

• XQuery методы: .query(), .value(), .exist(), .nodes(). Они используются для выборки данных, проверки существования и разбиения XML на строки, но не для модификации. XML_MODIFY уникальна именно для изменений.
• FOR XML: Позволяет создавать XML из реляционных данных. Это альтернативный способ генерации, а не модификации существующих XML-документов.
• OPENXML: Обеспечивает доступ к XML-данным как к реляционному набору строк. Полезна для чтения и вставки данных в таблицы, но не для точечного редактирования XML-столбца.

Функцию XML_MODIFY предпочтительнее использовать, когда требуется обновить лишь часть большого XML-документа, хранящегося в столбце или переменной, без его полной замены.

Типичные ошибки при работе с функцией

1. Целевой узел не является синглтоном

Инструкции replace value of и многие операции insert/delete требуют, чтобы выражение XPath однозначно идентифицировало один узел.

DECLARE @x xml = '<root><child>1</child><child>2</child></root>';
SET @x.modify('replace value of (/root/child/text())[1] with "new"'); -- Работает
-- SET @x.modify('replace value of (/root/child/text()) with "new"'); -- ОШИБКА: Требуется синглтон

2. Попытка обновить несколько узлов за один вызов

Один вызов modify() может содержать только одну инструкцию XML DML.

-- DECLARE @x xml = '<root><a/><b/></root>';
-- SET @x.modify('delete /root/a, delete /root/b'); -- ОШИБКА: Только одна инструкция

3. Неправильное использование sql:variable() или sql:column()

При передаче внешних значений в выражение DML их необходимо правильно экранировать.

DECLARE @x xml = '<root><elem>val</elem></root>';
DECLARE @NewVal nvarchar(10) = '<test>'; -- Значение с XML-символами
-- Прямая подстановка приведет к ошибке синтаксиса
-- SET @x.modify('replace value of (/root/elem/text())[1] with "<test>"');
-- Корректно: использование sql:variable()
SET @x.modify('replace value of (/root/elem/text())[1] with sql:variable("@NewVal")');
SELECT @x;

<root><elem>&lt;test&gt;</elem></root>

История изменений функции

Функция XML_MODIFY была введена в SQL Server 2005 вместе с типом данных xml. Существенных изменений в ее синтаксисе или поведении в последующих основных версиях (2008, 2012, 2014, 2016, 2017, 2019, 2022) не произошло.

Основные эволюционные изменения связаны с общими улучшениями производительности обработки XML и расширением соответствия стандартам XQuery в движке SQL Server. Рекомендуется использовать последние доступные накопительные обновления для получения исправлений ошибок, связанных с обработкой XML.

Расширенные сценарии применения

1. Модификация на основе данных из таблицы

DECLARE @Tbl TABLE (ID INT, DataXml XML);
INSERT INTO @Tbl VALUES (1, '<Items><Item ID="1">A</Item></Items>');

UPDATE @Tbl
SET DataXml.modify('insert <Item ID="2">B</Item> as last into (/Items)[1]')
WHERE ID = 1;

SELECT * FROM @Tbl;

ID DataXml
1  <Items><Item ID="1">A</Item><Item ID="2">B</Item></Items>

2. Условная вставка с использованием if

DECLARE @x xml = '<root><flag>true</flag></root>',
        @InsertIfTrue bit = 1;
-- Вставка узла только если переменная = 1
SET @x.modify('insert (
    if (sql:variable("@InsertIfTrue") = 1) then
        <newNode/>
    else
        ()
    )
    into (/root)[1]');
SELECT @x;

<root><flag>true</flag><newNode/></root>

3. Вставка скопированного содержимого другого узла

DECLARE @x xml = '<root><source>Copy this text</source><target/></root>';
SET @x.modify('insert /root/source/node()
               into (/root/target)[1]');
SELECT @x;

<root><source>Copy this text</source><target>Copy this text</target></root>

4. Удаление всех узлов, удовлетворяющих условию

DECLARE @x xml = '<Catalog><Product>A</Product><Product>B</Product><Service/></Catalog>';
SET @x.modify('delete /Catalog/Product');
SELECT @x;

<Catalog><Service/></Catalog>

5. Комплексное обновление с вычисляемым значением

DECLARE @x xml = '<Order Total="100"></Order>';
DECLARE @Discount INT = 10;
SET @x.modify('replace value of (/Order/@Total)[1]
               with (xs:decimal(/Order/@Total) - sql:variable("@Discount"))');
SELECT @x;

<Order Total="90"></Order>

Альтернативы в других СУБД и языках

Другие системы управления базами данных предлагают свои подходы к модификации XML.

PostgreSQL (модуль xml2)

-- Использование функции xpath_update для обновления значения
SELECT xmlparse(document '<root><a>1</a></root>') ||
       xpath_update('/root/a/text()', '2', true);

?column?
------------------------
<root><a>2</a></root>

Oracle (XML DB)

-- Использование функции UPDATEXML (устаревшая, в новых версиях рекомендуется XMLQuery, XMLTable)
SELECT UPDATEXML(
    XMLType('<root><child>old</child></root>'),
    '/root/child/text()',
    'new'
).getStringVal() FROM dual;

RESULT
--------------------------------------------------------
<root><child>new</child></root>

MySQL

MySQL не имеет прямой аналогии. Модификация обычно выполняется путем извлечения, преобразования на стороне приложения и полной замены документа или с помощью функций манипуляции строками для простых случаев.

Общий подход в приложениях

Во многих сценариях XML загружается в память приложения (например, с использованием DOM в Java, XmlDocument в .NET, ElementTree в Python), модифицируется с помощью методов соответствующего API и затем сохраняется обратно в базу данных. Это дает большую гибкость, но требует передачи всего документа между СУБД и приложением.