XML в Oracle PL/SQL (Часть 2 - Выборки в виде XML)

В предыдущей публикации были рассмотрены некоторые приёмы манипуляции с XML в Oracle, теперь рассмотрим как делать выборку данных напрямую в XML. Это на мой взгляд самая интересная часть.

На практике выяснилось, что для Oracle в большинстве случаев не существенно, вернуть ли набор записей или сформированную готовую XML этого набора записей — по времени выполнения эти действия субъективно практически равноценны. Но вот если у вас есть потребность в формировании некоторой XML структуры в приложении на основе данных полученных из БД Oracle, практически наверняка это будет довольно ресурсоемкое мероприятие, гораздо легче переложить этот функционал на базу данных, хотя на первый взгляд и кажется что это не то, чем должен заниматься движок базы данных.
Итак начнем.
Для экспериментов с выборками нам понадобится парочка таблиц, с которыми мы будем проводить эксперименты.

Создадим таблицу с городами:

create table CITY
(
  id   NUMBER not null, -- Идентификатор города
  name NVARCHAR2(100) not null -- Наименование города
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

И создадим таблицу с улицами:

create table STREET
(
  id      NUMBER not null, -- Идентификатор улицы
  id_city NUMBER not null, -- Идентификатор города
  name    NVARCHAR2(100) not null -- Наименование улицы
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

Заполним наши таблицы демонстрационными данными:

--Добавляем города
insert into CITY (ID, NAME) values (1, 'Красноярск');
insert into CITY (ID, NAME) values (2, 'Москва');
insert into CITY (ID, NAME) values (3, 'Новосибирск');
commit;

--Добавляем улицы
insert into STREET (ID, ID_CITY, NAME) values (1, 1, 'Мира');
insert into STREET (ID, ID_CITY, NAME) values (2, 1, 'Ленина');
insert into STREET (ID, ID_CITY, NAME) values (3, 2, 'Кутузова');
insert into STREET (ID, ID_CITY, NAME) values (4, 2, 'Ломоносова');
insert into STREET (ID, ID_CITY, NAME) values (5, 3, 'Чернышевского');
insert into STREET (ID, ID_CITY, NAME) values (6, 3, 'Карамзина');

Теперь всё готово, приступаем.

1. XMLElement — Выбор XML узла

В самом простом виде выборка в виде XML выглядит так:

select XMLElement("city",'Moscow') from dual;

Результат:

<city>Moscow</city>

Теперь проделаем то же самое с нашей таблицей CITY — выберем все города.

select XMLELEMENT("city",c.name) from city c;

Результат:

<city>Красноярск</city>
<city>Москва</city>
<city>Новосибирск</city>

В результате мы получили набор элементов, теперь научимся объединять все эти элементы в один родительский узел, это проще простого.

2. XMLAGG — Объединение (группировка) элементов в родительский узел.

Объединим все наши города из предыдущего примера в один родительский узел country.

select XMLELEment("country",XMLAGG(XMLELEMENT("city",c.name))) from city c;

Результат:

<country>
	<city>Красноярск</city>
	<city>Москва</city>
	<city>Новосибирск</city>
</country>

Как видим, в результате, мы получили элемент country с вложенным набором элементов city.

Теперь добавим название страны, и узнаем как добавлять атрибуты.

3. XMLATTRIBUTES — Добавление атрибутов в XML элемент.

Начнем с простого, добавим название страны.
Для этого надо просто после названия узла, добавить ещё один параметр XMLATTRIBUTES с перечисленными в скобках параметрами атрибутов.

select XMLELEment("country",xmlattributes('Россия' as "name"),XMLAGG(XMLELEMENT("city",c.name))) from city c;

Результат:

<country name="Россия">
	<city>Красноярск</city>
	<city>Москва</city>
	<city>Новосибирск</city>
</country>

Вот и всё что нам понадобится для выборки данных в виде XML, теперь попробуем сделать что нибудь более сложное.

Во первых, вынесем наименование города в атрибут, и добавим идентификатор города.

select XMLELEment("country",xmlattributes('Россия' as "name"),
       XMLAGG(XMLELEMENT("city",
       xmlattributes(c.id as "id", c.name as "name")))) from city c;

В результате получим XML вида:

<country name="Россия">
	<city id="1" name="Красноярск"></city>
	<city id="2" name="Москва"></city>
	<city id="3" name="Новосибирск"></city>
</country>

Теперь добавим городам дочерние элементы street (улицы).
Для этого нам придётся использовать группировку.
Присоединим таблицу street и сгруппируем по идентификатору и наименованию города.

select XMLELEment("country",xmlattributes('Россия' as "name"),
       XMLAGG(XMLELEMENT("city",
       xmlattributes(c.id as "id", c.name as "name"),
       XMLAGG(XMLELEMENT("street",
       xmlattributes(s.id as "id", s.name as "name")))
       )))
        from city c 
             left join street s on s.id_city=c.id
             group by c.id, c.name;

В результате получим XML вида:

<country name="Россия">
	<city id="1" name="Красноярск">
		<street id="1" name="Мира"></street>
		<street id="2" name="Ленина"></street>
	</city>
	<city id="2" name="Москва">
		<street id="3" name="Кутузова"></street>
		<street id="4" name="Ломоносова"></street>
	</city>
	<city id="3" name="Новосибирск">
		<street id="5" name="Чернышевского"></street>
		<street id="6" name="Карамзина"></street>
	</city>
</country>

Это пожалуй самое основное, что необходимо знать.
Далее можно развивать и комбинировать разные конструкции для получения в принципе любых XML.

На этом всё, комментарии и вопросы приветствуются.