Главная
Arenadata Postgres
Базовые операции
Запросы к БД
Использование сложных типов данных
XML

Тип данных XML

Евгения Кузина

В PostgreSQL тип XML предназначен для хранения данных в XML-формате. Для этого типа PostgreSQL проверяет структуру входящих XML-данных и предоставляет функционал для выполнения строго типизированных операций.

Тип XML может хранить XML-документы с правильной структурой (well-formed) и фрагменты содержимого (content fragments), корневой узел которых не является узлом документа. Фрагменты содержимого также могут иметь более одного элемента верхнего уровня.

ПРИМЕЧАНИЕ

В PostgreSQL тип данных XML и функции для обработки значений XML имеют ограничения совместимости. Для дополнительной информации обратитесь к статье XML Limits and Conformance to SQL/XML.

Создание XML-значений

Вы можете использовать опции, описанные ниже, для создания значений типа XML. Чтобы протестировать эти опции, создадим новую таблицу со столбцом типа XML:

CREATE TABLE xmldocs (id SERIAL PRIMARY KEY, xmldata XML);

Функция xmlparse

Функция xmlparse генерирует XML из строки символов. Она имеет следующий синтаксис:

xmlparse ({ DOCUMENT | CONTENT } <значение>)

Первый аргумент определяет вид значения XML. Это может быть XML-документ (DOCUMENT) или фрагмент содержимого (CONTENT).

Пример:

 INSERT INTO xmldocs (xmldata)
    VALUES
        (xmlparse (DOCUMENT '<?xml version="1.0"?><book><title>Hyperion</title><author>Dan Simmons</author></book>')),
        (xmlparse (CONTENT '<title>1984</title><author>George Orwell</author>'))
    RETURNING *;

Результат:

 id |                             xmldata
----+------------------------------------------------------------------
  1 | <book><title>Hyperion</title><author>Dan Simmons</author></book>
  2 | <title>1984</title><author>George Orwell</author>

PostgreSQL-преобразование

Следующий синтаксис позволяет преобразовывать строки символов в XML:

xml <значение>
<значение>::xml

Обратите внимание, что приведение типа XML не проверяет входные значения на соответствие объявлению типа документа (Document Type Declaration, DTD) и другим разновидностям схем XML.

Пример:

 INSERT INTO xmldocs (xmldata)
    VALUES
        (xml '<?xml version="1.0"?><book><title>Hyperion</title><author>Dan Simmons</author></book>'),
        ('<title>1984</title><author>George Orwell</author>'::xml)
    RETURNING *;

Результат тот же:

 id |                             xmldata
----+------------------------------------------------------------------
  1 | <book><title>Hyperion</title><author>Dan Simmons</author></book>
  2 | <title>1984</title><author>George Orwell</author>

Преобразование XML в строку символов

Чтобы создать строку символов из XML, используйте функцию xmlserialize:

xmlserialize ({ DOCUMENT | CONTENT } <значение> AS <тип>)

<тип> может быть типом данных character, character varying или text.

Пример:

SELECT xmlserialize (CONTENT xmldata AS text )
FROM xmldocs;

Результат:

                                     xmlserialize
---------------------------------------------------------------------------------------
 <?xml version="1.0"?><book><title>Hyperion</title><author>Dan Simmons</author></book>
 <title>1984</title><author>George Orwell</author>

В соответствии с SQL-стандартом функция xmlserialize — единственный способ преобразовать XML в символьные типы, но PostgreSQL также позволяет использовать функцию CAST, чтобы преобразовать XML значение:

SELECT CAST(xmldata AS text)
FROM xmldocs;

Когда приведение типов между символьной строкой и типом XML происходит без использования функций xmlparse и xmlserialize, параметр конфигурации сессии xmloption определяет тип значения XML. Это может быть DOCUMENT или CONTENT. Значение по умолчанию — CONTENT. Используйте команду SET, чтобы изменить параметр xmloption:

SET xmloption TO DOCUMENT;
/* или */
SET XML OPTION DOCUMENT;

Создание элементов XML

В этом разделе описываются функции и выражения, которые создают элементы XML из данных SQL. Например, их можно использовать для преобразования результатов SQL-запроса в формат XML.

xmlcomment

Функция xmlcomment создает XML-комментарий с указанным текстом.

xmlcomment ( <текст> ) → xml

<текст> не может содержать -- или заканчиваться на -, иначе результат будет невалидным XML-комментарием. Если текст null, то и результат функции null.

Пример:

SELECT xmlcomment ('This is a comment');

Результат:

        xmlcomment
--------------------------
 <!--This is a comment-->

xmlconcat

Функция xmlconcat объединяет отдельные XML-значения, чтобы создать одно, содержащее XML-фрагмент.

xmlconcat ( <xml> [, ...] ) → xml

Значения null опускаются. Результатом функции будет null, если все индивидуальные значения равны null.

Пример:

SELECT xmlconcat('<title>Hyperion</title>','<author>Dan Simmons</author>');

Результат:

                      xmlconcat
-----------------------------------------------------
 <title>Hyperion</title><author>Dan Simmons</author>

xmlelement

Выражение xmlelement создает XML-элемент с заданным именем, атрибутами и содержимым.

xmlelement ( NAME <имя>
    [, xmlattributes ( <значение_атрибута> [ AS <имя_атрибута> ] [, ...] ) ] [, <содержимое> [, ...]] ) → xml

Где:

<имя> и <имя_атрибута> — идентификаторы;
<значение_атрибута> и <содержимое> — выражения, которые могут возвращать любой тип данных PostgreSQL;
xmlattributes генерирует атрибуты XML-элемента;
<содержимое> включает значения, которые объединяются для формирования содержимого элемента.

Пример:

SELECT xmlelement (name book,
    xmlattributes('novel' as genre, current_date as record_date), 'Leo Tolstoy,', ' War and Peace');

Результат:

                                   xmlelement
--------------------------------------------------------------------------------
 <book genre="novel" record_date="2022-09-06">Leo Tolstoy, War and Peace</book>

Если значение атрибута ссылается на столбец, можно не указывать имя атрибута, xmlelement использует имя столбца в качестве имени атрибута.

Пример:

SELECT xmlelement (name book, xmlattributes(b.genre, b.public_year as year), a.name, ', ',b.title)
FROM books as b, authors as a
WHERE b.author_id = a.id;

Результат:

                                          xmlelement
----------------------------------------------------------------------------------------------
 <book genre="novel" year="1960">Harper Lee, To Kill a Mockingbird</book>
 <book genre="novel" year="1925">F. Scott Fitzgerald, The Great Gatsby</book>
 <book genre="fantasy" year="1955">J.R.R. Tolkien, The Lord of the Rings</book>
 <book genre="sci-fi" year="1949">George Orwell, 1984</book>
 <book genre="fantasy" year="1937">J.R.R. Tolkien, The Hobbit, or There and Back Again</book>
 <book genre="novel" year="1869">Leo Tolstoy, War and Peace</book>
 <book genre="sci-fi" year="1989">Dan Simmons, Hyperion</book>
 <book genre="sci-fi" year="1895">Herbert Wells, The Time Machine</book>

xmlforest

Выражение xmlforest создает последовательность XML-элементов с заданными именами и содержимым.

xmlforest (<содержимое> [ AS <имя> ] [, ...] ) → xml

Где <имя> — идентификатор, <содержимое> может быть любого типа.

Пример:

SELECT xmlforest(b.title, b.public_year as year, a.name as author)
FROM books as b, author as a
WHERE b.author_id = a.id;

Результат:

 <title>To the Lighthouse</title><year>1927</year><author>Virginia Woolf</author>
 <title>Mrs. Dalloway</title><year>1925</year><author>Virginia Woolf</author>
 <title>To Kill a Mockingbird</title><year>1960</year><author>Harper Lee</author>
 <title>The Great Gatsby</title><year>1925</year><author>F. Scott Fitzgerald</author>
 <title>The Hobbit, or There and Back Again</title><year>1937</year><author>J.R.R. Tolkien</author>
 <title>The Lord of the Rings</title><year>1955</year><author>J.R.R. Tolkien</author>
 <title>1984</title><year>1949</year><author>George Orwell</author>
 <title>War and Peace</title><year>1869</year><author>Leo Tolstoy</author>
 <title>Hyperion</title><year>1989</year><author>Dan Simmons</author>
 <title>The Time Machine</title><year>1895</year><author>Herbert Wells</author>

Обратите внимание, что последовательность XML-элементов не является валидным XML-документом, если она состоит из более чем одного элемента. Используйте функцию xmlelement с выражением xmlforest, чтобы сделать его валидным.

xmlpi

Выражение xmlpi создает инструкции обработки XML.

xmlpi ( NAME <имя> [, <содержимое> ] ) → xml

Где <имя> — идентификатор, <содержимое> может быть любого типа, но не может включать символы ?>.

Пример:

SELECT xmlpi(name php, 'echo "hello world";');

Результат:

            xmlpi
-----------------------------
 <?php echo "hello world";?>

xmlroot

Выражение xmlroot изменяет свойства корневого узла XML.

xmlroot ( xml, version {<text>|NO VALUE} [, standalone {YES|NO|NO VALUE} ] ) → xml

Если указаны параметры version и standalone, они заменяют значения в декларации версии корневого узла.

Пример:

SELECT xmlroot(xmlparse(document '<?xml version="1.1"?><content>example</content>'),
        version '1.0', standalone yes);

Результат:

                           xmlroot
--------------------------------------------------------------
 <?xml version="1.0" standalone="yes"?><content>example</content>

xmlagg

Функция xmlagg является агрегатной функцией. Она объединяет входящие значения по строкам.

Вызовем xmlagg для таблицы xmldocs, созданной выше:

SELECT xmlagg(xmldata) FROM xmldocs;

Результат:

<book><title>Hyperion</title><author>Dan Simmons</author></book><book><title>1984</title><author>George Orwell</author></book>;

Проверка свойств XML-значений

XML-предикаты в этом разделе позволяют определить, является ли значение XML документом или фрагментом содержимого, содержит ли оно указанный элемент XML и имеет ли правильную (well-formed) структуру.

IS DOCUMENT / IS NOT DOCUMENT

Выражения IS DOCUMENT/IS NOT DOCUMENT определяют, является ли значение XML документом или фрагментом содержимого.

<xml> IS DOCUMENT → boolean
<xml> IS NOT DOCUMENT → boolean

Если <xml> является документом, IS DOCUMENT возвращает true, а IS NOT DOCUMENT возвращает false. Если <xml> не является документом, IS DOCUMENT возвращает false, а IS NOT DOCUMENT возвращает true.

Примеры:

SELECT xmldata IS DOCUMENT FROM xmldocs;

Результат:

 ?column?
----------
 t
 f

SELECT xmldata IS NOT DOCUMENT FROM xmldocs;

Результат:

?column?
----------
 f
 t

xmlexists

Функция xmlexists вычисляет выражение XPath 1.0 (аргумент <текст>) с указанным значением <xml>. Функция возвращает false, если результатом вычисления является пустой набор узлов. Если вычисление возвращает какое-либо другое значение, результат выполнения xmlexists — true.

xmlexists ( <текст> PASSING [BY {REF|VALUE}] <xml> [BY {REF|VALUE}] ) → boolean

<xml> должен быть документом XML, а не фрагментом содержимого и не XML-значением. Функция возвращает null, если какой-либо аргумент равен null.

Обновим таблицу xmldocs, чтобы сделать XML-данные валидными:

UPDATE xmldocs
SET xmldata = xmlparse (DOCUMENT '<?xml version="1.0"?><book><title>1984</title><author>George Orwell</author></book>')
WHERE id = 2;

Вызовем функцию xmlexists, чтобы определить, существует ли title со значением Hyperion:

SELECT xmlexists('//title[text() = ''Hyperion'']' PASSING BY VALUE xmldata)
FROM xmldocs;

Результат:

xmlexists
-----------
 t
 f

Стандарт SQL поддерживает два механизма передачи аргумента XML из SQL: BY REF и BY VALUE. Согласно стандарту SQL, PostgreSQL принимает BY REF и BY VALUE, но игнорирует их. Для получения дополнительной информации обратитесь к статье Incidental Limits of the Implementation.

В стандарте SQL функция xmlexists также вычисляет выражения на языке XML Query, но PostgreSQL допускает только выражение XPath 1.0, как описано в статье Queries Are Restricted to XPath 1.0.

xml_is_well_formed

Эти функции проверяют, является ли текстовая строка значением XML с правильным форматом (well-formed), и возвращают результат типа Boolean.

xml_is_well_formed ( <text> ) → boolean
xml_is_well_formed_document ( <text> ) → boolean
xml_is_well_formed_content ( <text> ) → boolean

xml_is_well_formed_document проверяет, является ли текстовая строка правильно сформированным документом, а xml_is_well_formed_content — является ли текстовая строка правильно сформированным фрагментом.

xml_is_well_formed проверяет текстовую строку в соответствии со значением xmloption.

Пример:

SELECT xml_is_well_formed ('<book><title>Hyperion</title><author>Dan Simmons</author></book>');

Результат — true.

Можно использовать xml_is_well_formed, чтобы проверить, будет ли приведение к типу XML успешным. Функции xml_is_well_formed_document и xml_is_well_formed_content помогают определить, сможет ли функция xmlparse преобразовать текстовую строку в XML.

Обработка XML-данных

Для обработки значений XML PostgreSQL предлагает функции xpath и xpath_exists, которые вычисляют выражения XPath 1.0. xpath извлекает значения XML, а xpath_exists определяет, существуют ли указанные значения. В PostgreSQL также есть выражение xmltable, которое создает таблицу из значения XML.

Получение XML-значений

Функция xpath возвращает массив значений XML, соответствующих выражению XPath. Если выражение XPath возвращает скалярное значение, xpath возвращает массив из одного элемента.

xpath ( <xpath_выражение> text, <xml> xml [, <массив_пр_имен> text[] ] ) → xml[]

Где:

*<xpath_выражение> — выражение XPath 1.0, представленное в виде текста.

<xml> — правильно сформированный (well-formed) документ XML, он должен иметь один элемент корневого узла.
<массив_пр_имен> — массив сопоставлений пространств имен (необязательный параметр). Это массив массивов, состоящих из двух элементов. Первый элемент каждого вложенного массива — имя пространства имен (alias), второй — URI пространства имен.

Примеры:

SELECT xpath('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>',
ARRAY[ARRAY['my', 'http://example.com']]);

/* пространство имен по умолчанию (анонимное) */
SELECT xpath('//mydefns:b/text()', '<a xmlns="http://example.com"><b>test</b></a>',
             ARRAY[ARRAY['mydefns', 'http://example.com']]);

Результат обоих выражений одинаковый:

 xpath
--------
 {test}

Функция xpath_exists определяет, существуют ли элементы, соответствующие выражению XPath. Результат её выполнения — значение Boolean, определяющее будет ли возвращено какое-либо значение, кроме пустого набора узлов.

xpath_exists ( <xpath_выражение> text, <xml> xml [, <массив_пр_имен> text[] ] ) → boolean

Эта функция эквивалентна предикату xmlexists, но имеет дополнительный аргумент для сопоставления пространства имен.

Пример:

SELECT xpath_exists('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>',
ARRAY[ARRAY['my', 'http://example.com']]);

Результат true.

Создание таблицы из XML

Выражение xmltable создает таблицу на основе значения XML. xmltable допускается использовать только в выражении FROM.

xmltable имеет следующий синтаксис:

xmltable (
[ XMLNAMESPACES ( <uri_пространства_имен> AS <имя_пространства_имен> [, ...] ), ]
<выражение_строки> PASSING [BY {REF|VALUE}] <выражение_документа> [BY {REF|VALUE}]
COLUMNS <имя> { <тип> [PATH <выражение_столбца>]
            [DEFAULT <выражение_по_умолчанию>] [NOT NULL | NULL]| FOR ORDINALITY }
[, ...]
) → setof record

Где:

XMLNAMESPACES (необязательный параметр) — список определений пространств имен, разделенный запятыми, в котором <uri_пространства_имен> является выражением типа text, <имя_пространства_имен> — идентификатором. XMLNAMESPACES определяет пространства имен XML, используемые в документе, и их псевдонимы. Определение пространства имен по умолчанию не поддерживается.
<выражение_строки> — выражение XPath 1.0 типа text. xmltable использует <выражение_документа> в качестве контекста для <выражение_строки>, чтобы получить набор узлов XML и преобразовать их в выходные строки. Если <выражение_документа> равно null или <выражение_строки> возвращает пустой набор узлов или любое значение, отличное от набора узлов, xmltable не возвращает строк.
<выражение_документа> является контекстом для <выражение_строки>. Оно должно возвращать правильно сформированный XML-документ.
Выражение COLUMNS определяет столбцы, создаваемые в выходной таблице. <имя> и <тип> должны быть заданы для каждого столбца. Выражения PATH, DEFAULT и NOT NULL | NULL необязательные. Столбец с атрибутом FOR ORDINALITY заполняется номерами строк, начиная с 1. Атрибут FOR ORDINALITY может иметь только один столбец.
<выражение_столбца> — выражение XPath 1.0, которое вычисляется для каждой строки с текущим узлом из результата <выражение_строки>. Если <выражение_столбца> не задано, имя столбца используется как неявный путь.
<выражение_по_умолчанию> применяется, если <выражение_столбца> возвращает пустой набор узлов для текущей строки. Если не указано <выражение_по_умолчанию>, значение столбца устанавливается равным null.

Выполним следующий запрос, который преобразует данные XML в таблицу booklist:

CREATE TABLE booklist AS SELECT xml
   $$<books>
      <book id="01">
         <title>Hyperion</title>
         <author>Dan Simmons</author>
         <price>60</price>
      </book>
      <book id="02">
        <title>1984</title>
        <author>George Orwell</author>
        <price>45</price>
      </book>
   </books>$$ AS books;

Приведенный ниже запрос возвращает таблицу из значения XML, указанного выше:

SELECT xmltable.* FROM booklist,
   XMLTABLE ('/books/book' PASSING books
      COLUMNS
         id CHAR(2) PATH '@id' NOT NULL,
         title TEXT PATH 'title' NOT NULL,
         author TEXT PATH 'author' NOT NULL,
         price FLOAT PATH 'price' NOT NULL);

Результат:

 id |  title   |    author     | price
----+----------+---------------+-------
 01 | Hyperion | Dan Simmons   |    60
 02 | 1984     | George Orwell |    45

С выражением xmltable можно использовать агрегатные функции:

SELECT count(id) as total_books, avg(price) as avg_price FROM booklist1,
   XMLTABLE ('/books/book' PASSING books
      COLUMNS
         id CHAR(2) PATH '@id' NOT NULL,
         title TEXT PATH 'title' NOT NULL,
         author TEXT PATH 'author' NOT NULL,
         price FLOAT PATH 'price' NOT NULL);

Результат:

 total_books | avg_price
-------------+-----------
           2 |      52.5

Экспорт таблиц в XML

Следующие функции создают значения XML на основе содержимого реляционной таблицы:

table_to_xml ( <table> regclass, <nulls> boolean,
               <tableforest> boolean, <targetns> text ) → xml
query_to_xml ( <query> text, <nulls> boolean,
               <tableforest> boolean, <targetns> text ) → xml
cursor_to_xml ( <cursor> refcursor, <count> integer, <nulls> boolean,
                <tableforest> boolean, <targetns> text ) → xml

Функция table_to_xml создает значение XML из содержимого таблицы, переданного в качестве параметра <table>. Тип <regclass> принимает строки, идентифицирующие таблицу.

Функция query_to_xml выполняет SQL-запрос, переданный в качестве параметра <query>, и создает значение XML из набора результатов.

Функция cursor_to_xml извлекает указанное количество строк (параметр <count>) из курсора, переданного в параметре <cursor> и создает из них значение XML. Поскольку результаты этих функций хранятся в памяти, используйте cursor_to_xml, если вам нужно экспортировать большую таблицу.

Все эти функции имеют следующие общие параметры:

Параметр <nulls> определяет, следует ли включать в вывод значения null.
Параметр <targetns> определяет пространство имен XML. Если вам не нужно определять пространство имен, передайте пустую строку.

Если параметр <tableforest> равен false, результирующий XML-документ будет иметь дополнительные узлы <row>:

<имя_таблицы>
  <row>
    <имя_столбца1>данные</имя_столбца1>
    <имя_столбца2>данные</имя_столбца2>
  </row>
  ...
</имя_таблицы>

Если для <tableforest> установлено значение true, результат содержит несколько узлов <имя_таблицы> вместо <row>:

<имя_таблицы>
  <имя_столбца1>данные</имя_столбца1>
  <имя_столбца2>данные</имя_столбца2>
</имя_таблицы>

<имя_таблицы>
  ...
</имя_таблицы>

Например, у нас есть таблица authors:

 id |        name         |    country
----+---------------------+---------------
  1 | Harper Lee          | USA
  2 | F. Scott Fitzgerald | USA
  3 | J.R.R. Tolkien      | Great Britain

Выполните следующее выражение, чтобы преобразовать таблицу authors в XML:

SELECT table_to_xml ('authors', false, true, '' );

Результат:

 <authors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+
   <id>1</id>                                                   +
   <name>Harper Lee</name>                                      +
   <country>USA</country>                                       +
 </authors>                                                     +
                                                                +
 <authors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+
   <id>2</id>                                                   +
   <name>F. Scott Fitzgerald</name>                             +
   <country>USA</country>                                       +
 </authors>                                                     +
                                                                +
 <authors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+
   <id>3</id>                                                   +
   <name>J.R.R. Tolkien</name>                                  +
   <country>Great Britain</country>                             +
 </authors>                                                     +

Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней