Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите модифицировать xml  [new]
uaggster
Member

Откуда:
Сообщений: 673
Коллеги, приветствую!

Помогите модифицировать xml.
Хелп курил, не помогает.
Имеются 2 xml:
declare @x xml = N'<root>
<a>
<b>1</b>
</a>
<a>
<b>2</b>
</a>
<a>
<b>3</b>
</a>
</root>'

declare @y xml = N'<root>
<a>
<b>1</b>
<d>test</d>
</a>
<a>
<b>3</b>
<d><e>test</e></d>
</a>
</root>'


Необходимо модифицировать @х нодами из @y, чтобы на выходе получился такой хмл:
declare @x xml = N'<root>
<a>
<b>1</b>
<d>test</d>
</a>
<a>
<b>2</b>
</a>
<a>
<b>3</b>
<d><e>test</e></d>
</a>
</root>'

Т.е., в случае, если значение тега <b> в @x и @y - совпадает, то вставляем содержимое контейнера <а> из @y в @x, но без лидирующего тега <b>.

Начал так:

Declare @y_n as xml, @y_id as int 

Declare cur CURSOR LOCAL FORWARD_ONLY
	for
		select t.n.query('*') as y_n, t.n.value('(b)[1]','int')  as y_id from  @y.nodes('/root/a') t(n)


Set @x.modify(
'insert (sql:variable("@y_n"))              
	into (...)
	after (...) 
')

Open cur
FETCH NEXT FROM cur into @SL, @SL_ID

Чего писать тут?
into (...)
after (...)
Не могу продраться сквозь синтаксис, хоть убейся!
... Да и вставлять нужно без лидирующего <b>, поэтому t.n.query('*') - не получится.

Помогите, очень нужен пример!
12 авг 19, 16:25    [21947346]     Ответить | Цитировать Сообщить модератору
 Re: Помогите модифицировать xml  [new]
uaggster
Member

Откуда:
Сообщений: 673
Как всегда переврал синтаксис курсора в процессе подготовки поста, и уже не исправишь.
Курсоры я писать умею, не в этом вопрос. Нужна именно модификация xml.
:-)
12 авг 19, 16:29    [21947350]     Ответить | Цитировать Сообщить модератору
 Re: Помогите модифицировать xml  [new]
aleks222
Member

Откуда:
Сообщений: 709
1. Забить на курсоры.
2. Разобрать хмл в таблицы.
3. Из таблиц собрать новый хмл.
12 авг 19, 16:37    [21947359]     Ответить | Цитировать Сообщить модератору
 Re: Помогите модифицировать xml  [new]
uaggster
Member

Откуда:
Сообщений: 673
aleks222
1. Забить на курсоры.
2. Разобрать хмл в таблицы.
3. Из таблиц собрать новый хмл.

Нет, не вариант.
В оригинале, хмли - не очень большие по количеству нод (сотни - тысячи), но очень вычурные по их содержимому.
Тысячи тегов, переменного состава и т.д.
Разбирать их - не вариант.

Сервис планово-внезапно изменил представление данных, вынеся "эпизодические" данные в файл-сателлит.
Чтобы не переписывать огромный кусок всего - стоит задача "эмулировать как было", с наименьшими потерями.
12 авг 19, 16:56    [21947384]     Ответить | Цитировать Сообщить модератору
 Re: Помогите модифицировать xml  [new]
Konst_One
Member

Откуда:
Сообщений: 11419
xslt- преобразования надо делать
12 авг 19, 16:58    [21947386]     Ответить | Цитировать Сообщить модератору
 Re: Помогите модифицировать xml  [new]
invm
Member

Откуда: Москва
Сообщений: 8713
uaggster
Разбирать их - не вариант.
Их не надо разбирать
declare @x xml = N'<root>
<a>
<b>1</b>
</a>
<a>
<b>2</b>
</a>
<a>
<b>3</b>
</a>
</root>';

declare @y xml = N'<root>
<a>
<b>1</b>
<d>test</d>
</a>
<a>
<b>3</b>
<d><e>test</e></d>
</a>
</root>';

with x as
(
 select
  row_number() over (order by t.n) as rn,
  t.n.value('b[1]', 'int') as b,
  t.n.query('./*') as x
 from
  @x.nodes('/root/a') t(n)
)
select
 x.x as [*],
 y.n.query('./*[local-name() != "b"]') as [*]
from
 x outer apply
 @y.nodes('/root/a[b = sql:column("x.b")]') y(n)
for xml path('a'), root('root'), type;
12 авг 19, 17:15    [21947406]     Ответить | Цитировать Сообщить модератору
 Re: Помогите модифицировать xml  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1008
uaggster
aleks222
1. Забить на курсоры.
2. Разобрать хмл в таблицы.
3. Из таблиц собрать новый хмл.

Нет, не вариант.
В оригинале, хмли - не очень большие по количеству нод (сотни - тысячи), но очень вычурные по их содержимому.
Тысячи тегов, переменного состава и т.д.
Разбирать их - не вариант.

Сервис планово-внезапно изменил представление данных, вынеся "эпизодические" данные в файл-сателлит.
Чтобы не переписывать огромный кусок всего - стоит задача "эмулировать как было", с наименьшими потерями.

Через курсор то не проблема.

DECLARE @b int, @xml xml;

DECLARE cur CURSOR FAST_FORWARD FOR
SELECT t.c.value('b[1]', 'int') AS b
FROM @x.nodes('/root/a') AS t(c)
        INNER JOIN @y.nodes('/root/a') AS t1(c)
                ON t.c.value('b[1]', 'int') = t1.c.value('b[1]', 'int')
                
OPEN cur
FETCH NEXT FROM cur INTO @b

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @xml = @y.query('/root/a[b/text() = sql:variable("@b")]')

    SET @x.modify('delete (/root/a[b/text() = sql:variable("@b")])[1]')
    
    SET @x.modify('insert sql:variable("@xml") as last into /root[1]')
    FETCH NEXT FROM cur INTO @b
END

CLOSE cur
DEALLOCATE cur

SELECT @x


Мне казалось, есть более красивый вариант, но сейчас не смог воспроизвести.
12 авг 19, 17:24    [21947410]     Ответить | Цитировать Сообщить модератору
 Re: Помогите модифицировать xml  [new]
uaggster
Member

Откуда:
Сообщений: 673
Minamoto, практически то, что нужно, но порядок тегов - поменялся местами :-)

И я немного не понял, что Вы делали.
1. Запросом из 2 xml определяется перечень ид, которые нужно модифицировать.
Далее, в курсоре
2. Извлекаем запросом ветку с ид, равным значению из курсора.
3. Урезаем значение с лишним тегом b в @x??? (а почему в @x то, мы ж туда вставляем?). Так не пойдет, т.к. в оригинале у соответствующего тега в @x - тьма атрибутов, и b в @x и @y, скажем так, не взаимозаменяемы.
Но это не принципиально, т.к., как я понимаю, можно сделать:
SET @xml.modify('delete (/root/a[b/text() = sql:variable("@b")])[1]')

4. Не понятно. Вставляем вытащенную из @y ветку последней в root.
Так не пойдет.
А способа найти нужную ноду с соответствующим ид и вставить в нее - нет?
13 авг 19, 08:34    [21947666]     Ответить | Цитировать Сообщить модератору
 Re: Помогите модифицировать xml  [new]
uaggster
Member

Откуда:
Сообщений: 673
invm, да, формально это то, что нужно.
Только я решительно них... нихт, я хотел сказать, не понял.
Сейчас попробую применить к живым данным.
Кстати, а зачем там row_number() over (order by t.n) as rn?
Чтобы "материализовать" with?
13 авг 19, 08:42    [21947669]     Ответить | Цитировать Сообщить модератору
 Re: Помогите модифицировать xml  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1008
uaggster
Minamoto, практически то, что нужно, но порядок тегов - поменялся местами :-)

И я немного не понял, что Вы делали.
1. Запросом из 2 xml определяется перечень ид, которые нужно модифицировать.
Далее, в курсоре
2. Извлекаем запросом ветку с ид, равным значению из курсора.
3. Урезаем значение с лишним тегом b в @x??? (а почему в @x то, мы ж туда вставляем?). Так не пойдет, т.к. в оригинале у соответствующего тега в @x - тьма атрибутов, и b в @x и @y, скажем так, не взаимозаменяемы.
Но это не принципиально, т.к., как я понимаю, можно сделать:
SET @xml.modify('delete (/root/a[b/text() = sql:variable("@b")])[1]')

4. Не понятно. Вставляем вытащенную из @y ветку последней в root.
Так не пойдет.

Порядок тэгов в XML не фиксирован, как набор, возвращаемый запросом без order by ;)
Можно переделать, чтобы нужный порядок был, не проблема, но вариант invm лучше, используйте его. Если не получится, тогда переделаем.
3) Насколько я понял, нужно заменить все содержимое ноды a, в которой значение b присутствует в @y. Собственно это я и делаю - грохаю полностью ноду a, и вставляю ноду a из @y с совпадающим b. Если я неправильно понял постановку - проясните, что вы имели в виду.

uaggster
А способа найти нужную ноду с соответствующим ид и вставить в нее - нет?

Способ можно найти, если вы опишете, какую конкретно ноду вы ищете и куда ее собираетесь вставить. Пока это абсолютно неочевидно.
Ну и эти вопросы актуальны, если вам не подойдет вариант invm.
13 авг 19, 09:59    [21947726]     Ответить | Цитировать Сообщить модератору
 Re: Помогите модифицировать xml  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1008
uaggster
Кстати, а зачем там row_number() over (order by t.n) as rn?
Чтобы "материализовать" with?

Да низачем.

Можно даже "упростить":

select
    t.n.query('./*') as [*],
    y.n.query('./*[local-name() != "b"]') as [*]
from
    @x.nodes('/root/a') t(n)
    outer apply (values (t.n.value('b[1]', 'int'))) as t1(n)
    outer apply @y.nodes('/root/a[b = sql:column("t1.n")]') as y(n)
for xml path('a'), root('root'), type;
13 авг 19, 10:07    [21947732]     Ответить | Цитировать Сообщить модератору
 Re: Помогите модифицировать xml  [new]
invm
Member

Откуда: Москва
Сообщений: 8713
uaggster
Кстати, а зачем там row_number() over (order by t.n) as rn?
Чтобы "материализовать" with?
Можно убрать. Это остатки экспериментов.
13 авг 19, 10:24    [21947751]     Ответить | Цитировать Сообщить модератору
 Re: Помогите модифицировать xml  [new]
uaggster
Member

Откуда:
Сообщений: 673
Minamoto
uaggster
А способа найти нужную ноду с соответствующим ид и вставить в нее - нет?

Способ можно найти, если вы опишете, какую конкретно ноду вы ищете и куда ее собираетесь вставить. Пока это абсолютно неочевидно.
Ну и эти вопросы актуальны, если вам не подойдет вариант invm.

Видимо, я чрезмерно упростил пример.
Тогда вся предыстория.
Когда-то был единый хмл-файл обмена, небольшой по объему, меньше 100Мб, но довольно вычурный по структуре.
Некоторая информация в этом файле - повторялась. Т.е. он был - денормализованный. А у некоторых нод - был сокращенный, в сравнении с общим случаем, набор тегов.
Сейчас (сторонние) разработчики сделали то, что грозились - вынесли часть повторяющейся информации в отдельный файл. Ну, точнее - файлы, но действия там - однотипные.
В одном файле - некая уникальная (по ИД) последовательность главных нодов, в файлах - довесках - "дополнительные" довески к этим нодам, которые "расшиваются" в зависимости от, возможно повторяющихся ключей, значение которых имеется внутри этой родителькой ноды, но которые могут и не присутствовать для некоторых нод из родительского файла.
Нормализовали, они, короче говоря, файл.

Стоит задача - синтезировать из пары родительский файл - довесок - исходный "одиночный" файл.
Т.е. взять исходный файл, и добавить в него данные довесков, ориентируясь на совпадение ключевых тегов.

Т.е. если значение некого тега b, расположенного в контейнере а, совпали и в первом, и во втором файле, все теги из контейнера а второго файла должны быть добавлены в контейнер а первого файла, в идеале - в конец этого контейнера, с совпавшим ИД.

Вариант invm - хорош, и вроде бы - решает проблему, однако, скорее всего, использовать я его не смогу, т.к. в реальности xml имеет заголовок и подвал, да и места, куда надо добавить значения - они тоже спрятаны в глубине иерархии, причем находятся в ней на среднем где-то уровне.
Т.е. выглядит примерно как:
<root>
  <head>Тут куча тегов</head>
  <level1>
    <level2>
      <a>
        <b>1</b>
        <level3>Тут куча тегов</level3>
      </a>
      <a>
        <b>2</b>
      </a>
    </level2>
  </level1>
  <level1>
    <level2>
      <a>
        <b>3</b>
        <level3>Тут куча тегов</level3>
        <level3>Тут куча тегов</level3>
      </a>
    </level2>
  </level1>
  <foot>Тут куча тегов</foot>
</root>

Вот как то так.
Поэтому и родилась мысль - пройтись курсором и вставлять кусочки из второго файла.
13 авг 19, 13:27    [21948042]     Ответить | Цитировать Сообщить модератору
 Re: Помогите модифицировать xml  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1008
uaggster, тогда придется объединить оба подхода :)

Заодно сделал, чтобы порядок соблюдался.

DECLARE @b int, @xml xml;

DECLARE cur CURSOR FAST_FORWARD FOR
SELECT 
    t.c.value('b[1]', 'int') AS b
FROM 
    @x.nodes('//a') AS t(c)
                
OPEN cur
FETCH NEXT FROM cur INTO @b

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @xml = (
                select
                    t.n.query('./*') as [*],
                    y.n.query('./*[local-name() != "b"]') as [*]
                from
                    @x.nodes('//a[b/text() = sql:variable("@b")]') t(n)
                    outer apply @y.nodes('//a[b = sql:variable("@b")]') as y(n)
                for xml path('a')
    );
    
    SET @x.modify('insert sql:variable("@xml") after (//a[b/text() = sql:variable("@b")])[1]')
    
    SET @x.modify('delete (//a[b/text() = sql:variable("@b")])[1]')
    
    FETCH NEXT FROM cur INTO @b
END

CLOSE cur
DEALLOCATE cur

SELECT @x
13 авг 19, 13:54    [21948105]     Ответить | Цитировать Сообщить модератору
 Re: Помогите модифицировать xml  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1008
Minamoto
uaggster, тогда придется объединить оба подхода :)

Заодно сделал, чтобы порядок соблюдался.

ЗЫ: Весь подход расчитан на то, что значение в <b> уникально внутри файла @y. Если это может быть не так - то логика ломается, и нужно будет менять логику.
13 авг 19, 13:55    [21948109]     Ответить | Цитировать Сообщить модератору
 Re: Помогите модифицировать xml  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1008
Minamoto
ЗЫ: Весь подход расчитан на то, что значение в <b> уникально внутри файла @y. Если это может быть не так - то логика ломается, и нужно будет менять логику.

Внутри файла @x, конечно, тоже.
13 авг 19, 13:56    [21948110]     Ответить | Цитировать Сообщить модератору
 Re: Помогите модифицировать xml  [new]
uaggster
Member

Откуда:
Сообщений: 673
Minamoto
Minamoto
uaggster, тогда придется объединить оба подхода :)

Заодно сделал, чтобы порядок соблюдался.

ЗЫ: Весь подход расчитан на то, что значение в <b> уникально внутри файла @y. Если это может быть не так - то логика ломается, и нужно будет менять логику.

Вот внутри @x, куда вставляют - оно не уникально, а внутри @y, откуда вставляют - гарантированно уникально.
13 авг 19, 14:45    [21948173]     Ответить | Цитировать Сообщить модератору
 Re: Помогите модифицировать xml  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1008
uaggster
Вот внутри @x, куда вставляют - оно не уникально, а внутри @y, откуда вставляют - гарантированно уникально.

Шо ж из вас постановку то надо клещами вытягивать, вроде же не первый день на форуме :)

Вот, вводим суррогатный ключ, чтобы не путать ноды:

+ Уже пора под спойлер
declare @x xml = N'<root>
  <head>Тут куча тегов</head>
  <level1>
    <level2>
      <a>
        <b>1</b>
        <level3>Тут куча тегов</level3>
      </a>
      <a>
        <b>1</b>
        <level3>Тут другая куча тегов</level3>
      </a>
      <a>
        <b>2</b>
      </a>
      <c />
    </level2>
  </level1>
  <level1>
    <level2>
      <a>
        <b>3</b>
        <level3>Тут куча тегов</level3>
        <level3>Тут куча тегов</level3>
      </a>
    </level2>
  </level1>
  <foot>Тут куча тегов</foot>
</root>';

declare @y xml = N'<root>
<a>
<b>1</b>
<d>test</d>
</a>
<a>
<b>3</b>
<d><e>test</e></d>
</a>
</root>';



DECLARE @b int, @rn int, @xml xml;

DECLARE cur CURSOR FAST_FORWARD FOR
SELECT 
    ROW_NUMBER() OVER (ORDER BY t.c.value('b[1]', 'int')),
    t.c.value('b[1]', 'int') AS b
FROM 
    @x.nodes('//a') AS t(c)
                
OPEN cur
FETCH NEXT FROM cur INTO @rn, @b

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @xml = (
                select
                    t.n.query('./*') as [*],
                    y.n.query('./*[local-name() != "b"]') as [*]
                from
                    @x.nodes('(//a)[sql:variable("@rn")]') t(n)
                    outer apply @y.nodes('//a[b = sql:variable("@b")]') as y(n)
                for xml path('a')
    );
    
    SET @x.modify('insert sql:variable("@xml") after (//a)[sql:variable("@rn")][1]')
    
    SET @x.modify('delete (//a)[sql:variable("@rn")]')
    SELECT @x, @xml, @rn
    FETCH NEXT FROM cur INTO @rn, @b
END

CLOSE cur
DEALLOCATE cur

SELECT @x
13 авг 19, 14:56    [21948184]     Ответить | Цитировать Сообщить модератору
 Re: Помогите модифицировать xml  [new]
uaggster
Member

Откуда:
Сообщений: 673
Minamoto, большое спасибо. Наверное, это оно.
По моему, я даже понял решение.
Воспроизвести только, в чуть отличающемся случае - не смогу.

Вы какое-нибудь пособие for dummies по xQuery посоветовать не можете?
Потому, что я либо не тот MSDN читаю, либо не тем способом, либо воспринимаю не тем органом, который необходим для его восприятия.
13 авг 19, 15:28    [21948239]     Ответить | Цитировать Сообщить модератору
 Re: Помогите модифицировать xml  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1008
uaggster
Minamoto, большое спасибо. Наверное, это оно.
По моему, я даже понял решение.
Воспроизвести только, в чуть отличающемся случае - не смогу.

Вы какое-нибудь пособие for dummies по xQuery посоветовать не можете?
Потому, что я либо не тот MSDN читаю, либо не тем способом, либо воспринимаю не тем органом, который необходим для его восприятия.
Я вообще изучал XSLT и XPath не только относительно SQL Server, так понятнее и проще - потом уже можно применить в запросах, если понимать логику ключевых слов nodes, exist, value и остальных.

Изучал последовательно здесь:
http://www.xmlmaster.org/en/article/d01/index.html, собственно, для сдачи экзамена, экзамен сдал успешно )

Если с английским проблемы, то что-то полезное для общего понимания есть на Вики, тут:
https://ru.wikipedia.org/wiki/XML
и тут:
https://ru.wikipedia.org/wiki/XPath
13 авг 19, 15:38    [21948250]     Ответить | Цитировать Сообщить модератору
 Re: Помогите модифицировать xml  [new]
petalvik
Member

Откуда:
Сообщений: 690
uaggster
Вы какое-нибудь пособие for dummies по xQuery посоветовать не можете?


XQuery для Sql Server имеет свою специфику, поэтому лучше сразу учить правильный вариант.

Есть такая книга: XQuery language reference. Официально бесплатна. Можно скачать, например, здесь.
13 авг 19, 16:41    [21948339]     Ответить | Цитировать Сообщить модератору
 Re: Помогите модифицировать xml  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1008
petalvik
uaggster
Вы какое-нибудь пособие for dummies по xQuery посоветовать не можете?


XQuery для Sql Server имеет свою специфику, поэтому лучше сразу учить правильный вариант.

Есть такая книга: XQuery language reference. Официально бесплатна. Можно скачать, например, здесь.
Полностью согласен, лучше сразу учить правильный вариант - без специфики.

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

Начинать с конкретной реализации можно тогда, когда есть 100%-я уверенность, что никогда не придется пользоваться другими реализациями.

Ну и начинать изучение лучше все же с базы - базой является XML и XML Schema.
Далее, да, можно выбрать XQuery вместо XPath, т.к., из спецификации, "XQuery Version 1.0 is an extension of XPath Version 2.0."
Книга вроде неплохая, но начинать сразу с нее я бы не рекомендовал, инфа там дается, как я успел увидеть, посмотрев по диагонали, с расчетом на то, что читающий владеет базовой терминологией.
14 авг 19, 11:49    [21948988]     Ответить | Цитировать Сообщить модератору
 Re: Помогите модифицировать xml  [new]
uaggster
Member

Откуда:
Сообщений: 673
Коллеги, большое спасибо.
Ушел курить маны.
14 авг 19, 14:05    [21949248]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить