Замена содержимого в XML в среде MS SQL

#sql #sql_server #xml #xquery

Коллеги, есть такая задача по замене значений в поле data, где сообственно сам XML
документ.
Необходимо, чтобы производилась замена @yandex.ru на @mail.ru (логин,email) значения
по всем записям. Записей в таблице более 100. Каким образом реализовать проход по всем
записям с последовательной заменой значений?

Структура таблицы:

collaborator (id int, data xml)


Пример содержимого XML документа:


5759501959199724993
Рязанцев
Дмитрий
Александрович
499-0000000
ryazancevda@mail.ru
ryazancevda@mail.ru



Запрос:

declare @xml xml 
select @xml=data from collaborator
declare @email varchar(80)
declare @login varchar(80)

begin
update collaborator
    set @email = @xml.value('(/collaborator/email)[1]', 'varchar(80)')
    set @email = replace(@email, 'yandex.ru', 'mail.ru')
    set @xml.modify('
       replace value of (/collaborator/email/text())[1]
       with sql:variable("@email")
   ')

    set @login = @xml.value('(/collaborator/login)[1]', 'varchar(80)')
    set @login = replace(@login, 'yandex.ru', 'mail.ru')
    set @xml.modify('
       replace value of (/collaborator/login/text())[1]
       with sql:variable("@login")
   ')

 end
select @xml;

    

Ответы

Ответ 1

Я думал, думал... Придумал такое:

update collaborator
set data.modify('   
    replace value of (/collaborator/email/text())[1]
    with concat(
        substring(
            (/collaborator/email/text())[1],
            1,
            string-length((/collaborator/email/text())[1]) - string-length("mail.ru")),
        "yandex.ru")
')
where data.exist('/collaborator/email/text()[contains(., "mail.ru")]') = 1


update collaborator
set data.modify('   
    replace value of (/collaborator/login/text())[1]
    with concat(
        substring(
            (/collaborator/login/text())[1],
            1,
            string-length((/collaborator/login/text())[1]) - string-length("mail.ru")),
        "yandex.ru")
')
where data.exist('/collaborator/login/text()[contains(., "mail.ru")]') = 1


Получается два запроса. Это лучшее, что удалось придумать.

Обновить за раз можно только одну строку.

К тому же набор функций XQuery весьма ограничен, поэтому пришлось изобретать такую
сложную конструкцию с использованием concat/substring/string-length.



Если точно известно, что все логины и емейлы заканчиваются на "mail.ru", то можно
убрать условие where data.exist.

В коде неоднократно повторяются однотипные конструкции вида (/collaborator/login/text())[1],
что весьма громоздко. Не знаю, может есть способ использовать переменную let $login
:= ... и далее её подставлять?


Ответ 2

Если XML имеет такую простую структуру, то можно обойтись одним UPDATE:

UPDATE t
SET
    t.data = data.query('
        element collaborator
        {
            for $a in (/collaborator/*)
            return
                if (local-name($a) = "email")
                then element email { text { sql:column("d2.email") } }
                else if (local-name($a) = "login")
                then element login { text { sql:column("d2.login") } }
                else $a
        }
    ')
FROM
    collaborator t
    CROSS APPLY (
        SELECT
            email = t.data.value('(/collaborator/email/text())[1]', 'nvarchar(400)'),
            login = t.data.value('(/collaborator/login/text())[1]', 'nvarchar(400)')
    ) d
    CROSS APPLY (
        SELECT
            email = REPLACE(d.email, N'@mail.ru', N'@yandex.ru'),
            login = REPLACE(d.login, N'@mail.ru', N'@yandex.ru')
    ) d2


т.е. XML каждой строки данных пересобирается с помощью FLWOR, но в элементах email
и login значения заменяются выражениями возвращаемыми REPLACE.