Результаты SQL-запроса в формате XML

Результаты SQL-запроса в формате XML

v:1.0 26.12.2010
Петрелевич Сергей

Часто результаты sql-запроса надо представить в формате XML. Для этого в Oracle есть несколько средств. Рассмотрим одно из них.

Данные будем брать из этой таблицы

create table tXML
(
   Id   number, 
   Name varchar2(100),  
   constraint PK_tXML primary key (Id)
);

insert into tXML(Id,Name) values(1,'Name1');
insert into tXML(Id,Name) values(2,'Name2');
insert into tXML(Id,Name) values(3,'Name3');
insert into tXML(Id,Name) values(4,'Name4');
insert into tXML(Id,Name) values(5,'Name5');
insert into tXML(Id,Name) values(6,'Name6');
insert into tXML(Id,Name) values(7,'Name7');
insert into tXML(Id,Name) values(8,'Name8');
insert into tXML(Id,Name) values(9,'Name9');
insert into tXML(Id,Name) values(10,'Name10');

таким запросом

select xmlelement("document",
       xmlattributes('testId' as "DocumentID"),
       xmlagg(
              xmlelement("row", xmlforest(t.Id "Id", t.Name "Name"))
             )
                 ) as XMLresult
  from tXML t

В результате получим XMLType со следующим XML-документом:

<document DocumentID="testId">
<row>
 <Id>1</Id>
 <Name>Name1</Name>
</row>
<row>
 <Id>2</Id>
 <Name>Name2</Name>
</row>
<row>
 <Id>3</Id>
 <Name>Name3</Name>
</row>
<row>
 <Id>4</Id>
 <Name>Name4</Name>
</row>
 <row><Id>5</Id>
 <Name>Name5</Name>
</row>
<row>
 <Id>6</Id>
 <Name>Name6</Name>
</row>
<row>
 <Id>7</Id>
 <Name>Name7</Name>
</row>
<row>
 <Id>8</Id>
 <Name>Name8</Name>
</row>
<row>
 <Id>9</Id>
 <Name>Name9</Name>
</row>
<row>
 <Id>10</Id>
 <Name>Name10</Name>
</row>
</document>

Дам краткие пояснения по запросу.
XMLforest преобразует свои параметры в XML-элементы, полученные кусочки объединяет в итоговый XML-фрагмент. Если бы вместо него использовать два XMLelement'а для полей Id и Name, то на выходе вместо одной объединенной стоки получилось бы два отдельных столбца.
XMLelement создает из своих параметров XML-элемент.
XMLagg агрегирует (группирует) полученные фрагменты XML в итоговый XML-документ.
XMLattributes добавляет атрибут в XML-элементу.

А теперь все тоже самое, но по шагам.

Запрос:

  select XMLelement("Id",t.Id) as Id, XMLelement("Name",t.Name) as Name
    from tXML t;

В результате две отдельных колонки, в каждой по XML-элементу:

Id			Name
<Id>1</Id>	<Name>Name1</Name>
<Id>2</Id>	<Name>Name2</Name>
<Id>3</Id>	<Name>Name3</Name>
<Id>4</Id>	<Name>Name4</Name>
....

Запрос:

  select XMLforest(t.Id as "Id", t.Name as "Name") as Result
    from tXML t;

В результате одна колонка, в которой объединены XML-элементы:

RESULT
<Id>1</Id><Name>Name1</Name>
<Id>2</Id><Name>Name2</Name>
<Id>3</Id><Name>Name3</Name>
<Id>4</Id><Name>Name4</Name>
....

Запрос:

  select XMLelement("Row", XMLforest(t.Id as "Id", t.Name as "Name")) as Result
    from tXML t;

В результате отдельные XML-элементы формируются в XML-структуры:

RESULT
<Row><Id>1</Id><Name>Name1</Name></Row>
<Row><Id>2</Id><Name>Name2</Name></Row>
<Row><Id>3</Id><Name>Name3</Name></Row>
<Row><Id>4</Id><Name>Name4</Name></Row>
....

Запрос:

  select XMLagg(
          XMLelement("Row", XMLforest(t.Id as "Id", t.Name as "Name"))) 
         as Result
    from tXML t;

В результате отдельные XML-структуры объединяются в единый XML-документ:

RESULT (фактически это одна строка, отформатированная для лучшего восприятия)
<Row>
  <Id>1</Id><Name>Name1</Name>
</Row>
<Row>
  <Id>2</Id>
  <Name>Name2</Name>
</Row>
<Row>
  <Id>3</Id>
  <Name>Name3</Name>
</Row>
<Row>
  <Id>4</Id>
  <Name>Name4</Name>
</Row>....

Запрос:

  select XMLelement("document", XMLattributes('testID' as "DocumentID"),  
           XMLagg(
             XMLelement("Row", XMLforest(t.Id as "Id", t.Name as "Name")))) 
         as Result
    from tXML t;

В результате добавляется корневой элемент с атрибутом и получается законченный XML-документ:

RESULT (фактически это одна строка, отформатированная для лучшего восприятия)
<document DocumentID="testID">
  <Row>
    <Id>1</Id>
    <Name>Name1</Name>
  </Row>
  <Row>
    <Id>2</Id>
    <Name>Name2</Name>
  </Row>
  <Row>
    <Id>3</Id>
    <Name>Name3</Name>
  </Row>
  <Row>
    <Id>4</Id>
    <Name>Name4</Name>
  </Row>....
</document>

Все довольно-таки просто. Для формирования XML в Oracle есть все необходимое.

Метки: XML SQL

Комментарии.

Внимание.
Комментировать могут только зарегистрированные пользователи.
Возможно использование следующих HTML тегов: <a>, <b>, <i>, <br>.

unbroken	Mar 24, 2021 4:46:43 PM
хорошая статья ,спасибо!

Содержание

Результаты SQL-запроса в формате XML