SQL, Анализ данных

Применение функции ХMLAGG в сочетании SQL

Время прочтения: 2 мин.

Применение Oracle (9 версии и выше) предоставляет такую возможность: использование  функций ХML-related в SQL- запросах. XML -функции в запросах к реляционным данным позволяют получить данные в формате XML.

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

Для начала предлагаю ознакомиться с основными SQL/ХML функциями, которые наиболее распространены и применяются на практике.

Итак, набор доступных функций конструирования включает в себя:

XMLELEMENT
XMLATTRIBUTES
XMLFOREST
XMLCONCAT
XMLNAMESPACES
XMLCOMMENT
XMLPI
XMLDOCUMENT
XMLAGG
и др. 

Так, например, XMLELEMENT-функция предполагает в качестве параметров рассматривать наименование элемента (результата), атрибуты элемента и аргумент, который представляет содержимое результирующего элемента. Конструкция XMLATTRIBUTES применяется для отражения атрибутов тэга. Функция XMLFOREST изменяет заданные параметры в соответствующий тэг.

Одной из полезных функций создания ХML-данных является функция ХMLAGG. С помощью данной функции возможно объединять небольшие XML-фрагменты в более крупные. ХMLAGG – это агрегационная функция, которая при этом может применяться в сочетании с оператором GROUP BY.

В настоящей статье я хочу поделиться своим опытом применения функции ХMLAGG и предлагаю ознакомиться с примером ее использования в запросе SQL. Мне было необходимо сгруппировать в одну строку номера счетов с одинаковыми ИНН и КПП. Итак,

select c.inn 
    ,c.kpp

     --объединение поля TEXT_ATTR_01, в одну большую строку, группируя по полям c. Inn, c. Kpp

    ,rtrim(xmlagg(XMLELEMENT(e,c.TEXT_ATTR_01,',').EXTRACT('//text()')).GetClobVal(),', ') as TEXT_ATTR_01
from (select a.*

     -- конструкция, при помощи которой выполняется задача объединения данных поля TEXT_ATTR_01 через запятую с группировкой по полям c inn, c.kpp    
   
    ,coalesce(b.kpp,'0') kpp
    from MA_TMP.rest_1905 a left join MA_CMDM.MA_UNIFIED_CUSTOMER b

    -- MA_TMP.rest_1905, MA_CMDM.MA_UNIFIED_CUSTOMER – наименование таблиц;
    ON a.inn=b.inn) c
group by c.inn, c.kpp

    -- наименование полей, по которым осуществляется группировка

Таким образом, использование данной функции позволяет объединить все значения из столбца таблицы, группируя их по определенному параметру. Преимуществом функции ХMLAGG является соединение большого объёма данных.         

Указанный пример наглядно демонстрирует, как применение функции ХMLAGG в сочетании SQL может помочь быстро и эффективно справиться с поставленной задачей, а также сократить время и трудоемкость аудиторских проверок.

Советуем почитать