XML в Oracle - это очень просто

Автор: Владимир Пржиялковский Сайт: http://protoplex.ru/

   Введение
   Тип XMLTYPE появился в Oracle в версии 9.0. До этого наиболее подходящим для хранения документов в формате XML был тип CLOB (и менее подходящим – тип VARCHAR2, ограниченный максимумом 4000 знаков). Сам по себе объектный, новый тип XMLTYPE технически может храниться либо по-прежнему в виде CLOB, либо в виде объекта (начиная с версии 9.2). И еще одно замечание: несмотря на то, что технологии XML и Java идут «рука об руку», рамки приводимых ниже примеров не требуют от вашей БД установленных возможностей Java.

   В этой заметке рассмотрены только логические стороны использования XML в Oracle безотносительно к техническим свойствам хранения и доступа.
   Простой пример

   CREATE TABLE books
    (id NUMBER PRIMARY KEY
    , description XMLTYPE);

   INSERT INTO books VALUES
    (100
    , XMLTYPE('<cover>
    <title>Oracle SQL*Loader</title>
    <author>Jonathan Gennick</author>
    <author>Sanjay Mishra</author>
    <pages>269</pages>
    </cover>'));

   SET long 1000

   SELECT id, description FROM books;

   SELECT id, b.description.XMLDATA FROM books b;

   XMLDATA – специально созданный для XMLTYPE «псевдостолбец».
   XMLTYPE – тип XML

   XMLTYPE дает возможность сообщить БД, что заносимый текст – это не просто строка, а строка документа XML. Следующая попытка приведет к ошибке:

   INSERT INTO books VALUES (101, XMLTYPE('<cover><title></title>'));

   С дугой стороны, Oracle поймет правильно составленные директивы XML и встроенное в текст описание DTD:

   INSERT INTO books VALUES
    (101
    , XMLTYPE('<?xml version="1.0"?>
    <!DOCTYPE cover [
    <!ELEMENT cover (title, author*, pages)>
    <!ELEMENT title (#PCDATA)>
    <!ELEMENT author (#PCDATA)>
    <!ELEMENT pages (#PCDATA)>
    ]>
    <cover>
    <title>SQL*Plus Pocket Reference</title>
    <author>Jonathan Gennick</author>
    <pages>94</pages>
    </cover>'));

   Убедитесь в этом сами, что Oracle действительно соотносит описание DTD самому тексту документа !

   Для выборки можно использовать специально придуманные для XMLTYPE функции. Так, функция EXTRACTVALUE извлекает значения элемента из документа XML:

   SELECT id, EXTRACTVALUE(description, '/cover/title')
   FROM books;

   Функция EXISTSNODE дает возможность использовать в SQL условие отбора XPath (язык отбора, принятый в технологиях XML):

   SELECT id, b.description.XMLDATA
   FROM books b
   WHERE b.description.EXISTSNODE('/cover[author="Sanjay Mishra"]')=1;
   XMLTYPE – объектный тип Oracle

   Доказательством утверждения в заголовке служит создание следующей таблицы объектов типа XMLTYPE, «таблицы документов XML»:

   CREATE TABLE xbooks OF XMLTYPE;

   Работать с ними можно, как и с XML-атрибутом в обычной таблице:

   INSERT INTO xbooks VALUES
    (XMLTYPE('<cover>
    <title>Oracle SQL*Loader</title>
    <author>Jonathan Gennick</author>
    <author>Sanjay Mishra</author>
    <pages>269</pages>
    </cover>'));

   INSERT INTO xbooks VALUES
    (NEW XMLTYPE('<?xml version="1.0"?>
    <cover>
    <title>SQL*Plus Pocket Reference</title>
    <author>Jonathan Gennick</author>
    <pages>94</pages>
    </cover>'));

   В первом случае объект XML создается с помощью конструктора, а во втором, к тому же, используется оператор NEW. Последний применяется в Oracle для работы с объектами, однако его использование носит лишь рекомендательный характер, так как в SQL он ничего содержательного не дает.

   Далее:

   SELECT * FROM xbooks;

   SELECT VALUE(x) FROM xbooks x;

   SELECT XMLDATA FROM xbooks;

   Так же как для таблиц объектов прочих типов, элементы таблицы объектов XML имеют ссылки, то есть позволяют ссылаться на себя через REF в других типах и таблицах:

   SELECT REF(x) FROM xbooks x;

   SELECT DEREF(REF(x)) FROM xbooks x;

   У этого типа нет свойств, но есть методы. В этом можно убедиться, сделав запрос от имени SYS:

   COLUMN text FORMAT A80

   SELECT text
   FROM user_source
   WHERE name ='XMLTYPE' AND type='TYPE'
   ORDER BY line;

   Исследование каталога rdbms/admin позволяет обнаружить и исходное описание этого типа (но не его тела !) в файле dbmsxmlt.sql. К сожалению в документации описания этих методов разбросаны по разным местам, не всегда последовательны и ясны. Так например, EXTRACT и EXISTSNODE (о последней речь шла выше), возведены в ранг функций SQL, то есть описаны в книжке документации по SQL в разделе «Функции», в то время как из предыдущего запроса к словарю-справочнику следует, что это методы. О том же говорит синтаксис употребления. Для EXISTSNODE пример уже приводился, а для EXTRACT он может выглядеть так:

   SELECT b.description.EXTRACT('/cover/title') FROM books b;

   (Сравните с примером использования функции EXTRACTVALUE выше).

   Вот некоторые другие примеры методов XMLTYPE:

   SELECT b.description.GETCLOBVAL() FROM books b;

   SELECT b.description.GETSTRINGVAL() FROM books b;

   SELECT b.description.GETROOTELEMENT() FROM books b;

   Обратите внимание, что некоторые методы XMLTYPE, например TOOBJECT, могут использоваться только процедурно, так как сами исполнены в виде процедур, а не функций.

   Правда, объектность типа XMLTYPE реализована не в полной степени. Так, попытка создать в таблице столбец из коллекции документов XML (вложенной таблицы или массива VARRAY) в версии 9.2 терпит неудачу. Это относится только к БД; в PL/SQL этих проблем не возникает:

   SQL> declare type xml_nt is table of xmltype index by varchar2(10);
    2 begin null; end;
    3 /

   PL/SQL procedure successfully completed.
   Взаимные преобразования табличного вида и XMLTYPE

   Связь двух форм описания данных – табличной и XML – достигается не одною только возможностью создавать в таблицах столбец типа XMLTYPE. Возможно преобразование данных из одного вида в другой, благодаря чему исходный формат хранения данных может оказаться не столь существенен.
   Преобразование из XMLTYPE в табличную форму

   Для преобразования данных типа XMLTYPE в обычный табличный вид можно использовать функции SQL и методы XMLTYPE, в первую очередь упоминавшуюся метод-функцию EXTRACT:

   COLUMN xdoc FORMAT A80

   SELECT ROWNUM, id, b.description.EXTRACT('/cover/author') xdoc
   FROM books b;

   Обратите внимание на возможность и способ обработки нескольких авторов в XML элементах <author>.

   Использование функции SQL EXTRACTVALUE, в свою очередь, оставляет возможность отбора не более одного элемента XML для формирования каждой строки результата SELECT, но зато безболезнено убирает обрамляющие значение элемента XML метки:

   SELECT id, EXTRACTVALUE(b.description.EXTRACT('/cover/title'), '/title') xdoc
   FROM books b;

   То же самое можно записать проще, что уже демонстрировалось в начале статьи.
   Преобразование из табличной формы в XMLTYPE

   Для обратного преобразования удобно воспользоваться функциями, объединенными в стандарте SQL:2003 названием SQL/XML (другое название – SQLX). В версии Oracle 9.2 реализованы следующие (не все) функции из этого стандартного набора:

   - XMLElement
   - XMLAttributes
   - XMLAgg
   - XMLConcat
   - XMLForest

   Вот некоторые примеры использования в схеме SCOTT:

   SELECT XMLELEMENT("Employee", ename) FROM emp;

   SELECT XMLELEMENT("Employee",
    XMLATTRIBUTES(ename AS "Name", empno AS "Number"))
   FROM emp;

   Обратите внимание, что в результатах выдаются поля типа XMLTYPE:

   CREATE TABLE xtable (n) AS SELECT XMLELEMENT("Name", ename) FROM emp;

   DESCRIBE xtable

   Следующий пример – агрегирующей функции XMLAGG, допускающей использование в запросах с группировкой GROUP BY, подобно тому, как агрегирующие функции MIN, AVG и другие применяются для обычных данных, а не XMLTYPE:

   SET LONG 2000

   SELECT XMLELEMENT("department", XMLATTRIBUTES(deptno AS "no")),
    XMLAGG(XMLELEMENT("employee", ename))
   FROM emp
   GROUP BY deptno;

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

   CREATE VIEW xview (a, b) AS
   SELECT XMLELEMENT("department", XMLATTRIBUTES(deptno AS "no")),
    XMLAGG(XMLELEMENT("employee", ename))
   FROM emp
   GROUP BY deptno;

   (срабатывает)

   CREATE TABLE xtable (a, b) AS
   SELECT XMLELEMENT("department", XMLATTRIBUTES(deptno AS "no")),
    XMLAGG(XMLELEMENT("employee", ename))
   FROM emp
   GROUP BY deptno;

   (ошибка !)

   Это объясняется тем, что столбцы A и B в обоих случаях Oracle пытается создавать как XMLTYPE, а наши данные таковы, что в столбце B содержатся строго говоря некорректные строки XML, например

   <employee>CLARK</employee><employee>KING</employee> ....

   Однако в случае выводимой таблицы Oracle смотрит на это сквозь пальцы, а в случае базовой – нет. Возможно это есть следствие определенной недоработанности некоторых областей технологий XML в Oracle, что вызвано чересчур быстрыми темпами развития этих технологий.