Замена содержимого в 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.

Работа с XML в MS SQL?

#sql #xml #sql_server #xquery

Допустим, есть XML такого вида:















Можно ли получить выборку такого вида?

value
---------------
значение field1
значение field2
значение field3
значение field1
значение field2
значение field3


Можно ли это сделать без явного перечисления названия узлов? Допустим, известно,
что все узлы field+цифра. Можно ли сделать запрос по маске?
    

Ответы

Ответ 1

Вы можете отфильтровать узлы с нужными именами с помощью функций local-name и substring:

declare @xml xml = N'

  
    value 1
    value 2
    value 3
    AA
  
  
    value 4
    value 5
    value 6
    BB
  
';

select x.c.value('text()[1]', 'varchar(20)') value
from @xml.nodes('/root[1]/TableRow/*[substring(local-name(), 0, 6)="field"]') x(c);


Результат:

value
--------
value 1
value 2
value 3
value 4
value 5
value 6


В XQuery запросах использовать * в XPath (также как и выражения наподобие '//field'),
если без них можно обойтись, не рекомендуется. Если выбираете узлы от корня, то лучше
указать '/root[1]' в начале XPath. Если точно известен путь до узлов - указывайте его
явно. Если берёте значение из элемента, то лучше взять его не через ., а через text()[1].
Чем больше информации об узлах вы дадите XQuery процессору, тем проще будет ему распарсить
xml и тем ниже будет стоимость запроса.

В данном случае, например, оценочная стоимость такого запроса

select x.c.value('text()[1]', 'varchar(20)') value
from @xml.nodes('/root[1]/TableRow/*[substring(local-name(), 0, 6)="field"]') x(c);


и такого

select x.c.value('.', 'varchar(20)') value
from @xml.nodes('*/*/*[substring(local-name(), 0, 6)="field"]') x(c);


соотносятся примерно как 2 к 136.


Ответ 2

Должен сработать такой запрос:

select x.value('.', 'nvarchar(max)')
from [TableName]
cross apply [XmlField].nodes('*/*/*') t(x);


В выражении */*/* каждая звездочка соответствует любому имени на данном уровне вложенности.

Работа с XML в MS SQL?

Допустим, есть XML такого вида:

Можно ли получить выборку такого вида?
value --------------- значение field1 значение field2 значение field3 значение field1 значение field2 значение field3
Можно ли это сделать без явного перечисления названия узлов? Допустим, известно, что все узлы field+цифра. Можно ли сделать запрос по маске?

Ответ

Вы можете отфильтровать узлы с нужными именами с помощью функций local-name и substring
declare @xml xml = N' value 1 value 2 value 3 AA value 4 value 5 value 6 BB ';
select x.c.value('text()[1]', 'varchar(20)') value from @xml.nodes('/root[1]/TableRow/*[substring(local-name(), 0, 6)="field"]') x(c);
Результат:
value -------- value 1 value 2 value 3 value 4 value 5 value 6
В XQuery запросах использовать * в XPath (также как и выражения наподобие '//field'), если без них можно обойтись, не рекомендуется. Если выбираете узлы от корня, то лучше указать '/root[1]' в начале XPath. Если точно известен путь до узлов - указывайте его явно. Если берёте значение из элемента, то лучше взять его не через , а через text()[1]. Чем больше информации об узлах вы дадите XQuery процессору, тем проще будет ему распарсить xml и тем ниже будет стоимость запроса.
В данном случае, например, оценочная стоимость такого запроса
select x.c.value('text()[1]', 'varchar(20)') value from @xml.nodes('/root[1]/TableRow/*[substring(local-name(), 0, 6)="field"]') x(c);
и такого
select x.c.value('.', 'varchar(20)') value from @xml.nodes('*/*/*[substring(local-name(), 0, 6)="field"]') x(c);
соотносятся примерно как 2 к 136.