Oracle xml добавить узел
Владимир Пржиялковский , преподаватель технологий Oracle
Введение
Тип 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);
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(' '));
С дугой стороны, Oracle поймет правильно составленные директивы XML и встроенное в текст описание DTD:
Убедитесь в этом сами, что 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-атрибутом в обычной таблице:
В первом случае объект 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 элементах .
Использование функции 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;
Следующий пример – агрегирующей функции XMLAGG, допускающей использование в запросах с группировкой GROUP BY, подобно тому, как агрегирующие функции MIN, AVG и другие применяются для обычных данных, а не XMLTYPE:
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, например
Однако в случае выводимой таблицы Oracle смотрит на это сквозь пальцы, а в случае базовой – нет. Возможно это есть следствие определенной недоработанности некоторых областей технологий XML в Oracle, что вызвано чересчур быстрыми темпами развития этих технологий.
XMLElement takes an element name for identifier or evaluates an element name for EVALNAME value_expr , an optional collection of attributes for the element, and arguments that make up the content of the element. It returns an instance of type XMLType . XMLElement is similar to SYS_XMLGen except that XMLElement can include attributes in the XML returned, but it does not accept formatting using the XMLFormat object.
The XMLElement function is typically nested to produce an XML document with a nested structure, as in the example in the following section.
For an explanation of the ENTITYESCAPING and NONENTITYESCAPING keywords, refer to Oracle XML DB Developer's Guide .
You must specify a value for Oracle Database to use an the enclosing tag. You can do this by specifying identifier , which is a string literal, or by specifying EVALNAME value_expr . In the latter case, the value expression is evaluated and the result, which must be a string literal, is used as the identifier. The identifier does not have to be a column name or column reference. It cannot be an expression or null. It can be up to 4000 characters if the initialization parameter MAX_STRING_SIZE = STANDARD , and 32767 characters if MAX_STRING_SIZE = EXTENDED .
The objects that make up the element content follow the XMLATTRIBUTES keyword. In the XML_attributes_clause , if the value_expr is null, then no attribute is created for that value expression. The type of value_expr cannot be an object type or collection. If you specify an alias for value_expr using the AS clause, then the c_alias or the evaluated value expression ( EVALNAME value_expr ) can be up to 4000 characters if the initialization parameter MAX_STRING_SIZE = STANDARD , and 32767 characters if MAX_STRING_SIZE = EXTENDED .
"Extended Data Types" for more information on MAX_STRING_SIZE
For the optional value_expr that follows the XML_attributes_clause in the diagram:
If value_expr is a scalar expression, then you can omit the AS clause, and Oracle uses the column name as the element name.
If value_expr is an object type or collection, then the AS clause is mandatory, and Oracle uses the specified c_alias as the enclosing tag.
If value_expr is null, then no element is created for that value expression.
The following example produces an Emp element for a series of employees, with nested elements that provide the employee's name and hire date:
The following similar example uses the XMLElement function with the XML_attributes_clause to create nested XML elements with attribute values for the top-level element:
Notice that the AS identifier clause was not specified for the last_name column. As a result, the XML returned uses the column name last_name as the default.
Finally, the next example uses a subquery within the XML_attributes_clause to retrieve information from another table into the attributes of an element:
XMLElement takes an element name for identifier or evaluates an element name for EVALNAME value_expr , an optional collection of attributes for the element, and arguments that make up the content of the element. It returns an instance of type XMLType . XMLElement is similar to SYS_XMLGen except that XMLElement can include attributes in the XML returned, but it does not accept formatting using the XMLFormat object.
The XMLElement function is typically nested to produce an XML document with a nested structure, as in the example in the following section.
For an explanation of the ENTITYESCAPING and NONENTITYESCAPING keywords, refer to Oracle XML DB Developer's Guide .
You must specify a value for Oracle Database to use an the enclosing tag. You can do this by specifying identifier , which is a string literal, or by specifying EVALNAME value_expr . In the latter case, the value expression is evaluated and the result, which must be a string literal, is used as the identifier. The identifier does not have to be a column name or column reference. It cannot be an expression or null. It can be up to 4000 characters if the initialization parameter MAX_STRING_SIZE = STANDARD , and 32767 characters if MAX_STRING_SIZE = EXTENDED .
The objects that make up the element content follow the XMLATTRIBUTES keyword. In the XML_attributes_clause , if the value_expr is null, then no attribute is created for that value expression. The type of value_expr cannot be an object type or collection. If you specify an alias for value_expr using the AS clause, then the c_alias or the evaluated value expression ( EVALNAME value_expr ) can be up to 4000 characters if the initialization parameter MAX_STRING_SIZE = STANDARD , and 32767 characters if MAX_STRING_SIZE = EXTENDED .
"Extended Data Types" for more information on MAX_STRING_SIZE
For the optional value_expr that follows the XML_attributes_clause in the diagram:
If value_expr is a scalar expression, then you can omit the AS clause, and Oracle uses the column name as the element name.
If value_expr is an object type or collection, then the AS clause is mandatory, and Oracle uses the specified c_alias as the enclosing tag.
If value_expr is null, then no element is created for that value expression.
The following example produces an Emp element for a series of employees, with nested elements that provide the employee's name and hire date:
The following similar example uses the XMLElement function with the XML_attributes_clause to create nested XML elements with attribute values for the top-level element:
Notice that the AS identifier clause was not specified for the last_name column. As a result, the XML returned uses the column name last_name as the default.
Finally, the next example uses a subquery within the XML_attributes_clause to retrieve information from another table into the attributes of an element:
XMLElement takes an element name for identifier or evaluates an element name for EVALNAME value_expr , an optional collection of attributes for the element, and arguments that make up the content of the element. It returns an instance of type XMLType . XMLElement is similar to SYS_XMLGen except that XMLElement can include attributes in the XML returned, but it does not accept formatting using the XMLFormat object.
The XMLElement function is typically nested to produce an XML document with a nested structure, as in the example in the following section.
For an explanation of the ENTITYESCAPING and NONENTITYESCAPING keywords, refer to Oracle XML DB Developer's Guide .
You must specify a value for Oracle Database to use an the enclosing tag. You can do this by specifying identifier , which is a string literal, or by specifying EVALNAME value_expr . In the latter case, the value expression is evaluated and the result, which must be a string literal, is used as the identifier. The identifier does not have to be a column name or column reference. It cannot be an expression or null. It can be up to 4000 characters if the initialization parameter MAX_STRING_SIZE = STANDARD , and 32767 characters if MAX_STRING_SIZE = EXTENDED .
The objects that make up the element content follow the XMLATTRIBUTES keyword. In the XML_attributes_clause , if the value_expr is null, then no attribute is created for that value expression. The type of value_expr cannot be an object type or collection. If you specify an alias for value_expr using the AS clause, then the c_alias or the evaluated value expression ( EVALNAME value_expr ) can be up to 4000 characters if the initialization parameter MAX_STRING_SIZE = STANDARD , and 32767 characters if MAX_STRING_SIZE = EXTENDED .
"Extended Data Types" for more information on MAX_STRING_SIZE
For the optional value_expr that follows the XML_attributes_clause in the diagram:
If value_expr is a scalar expression, then you can omit the AS clause, and Oracle uses the column name as the element name.
If value_expr is an object type or collection, then the AS clause is mandatory, and Oracle uses the specified c_alias as the enclosing tag.
If value_expr is null, then no element is created for that value expression.
The following example produces an Emp element for a series of employees, with nested elements that provide the employee's name and hire date:
The following similar example uses the XMLElement function with the XML_attributes_clause to create nested XML elements with attribute values for the top-level element:
Notice that the AS identifier clause was not specified for the last_name column. As a result, the XML returned uses the column name last_name as the default.
Finally, the next example uses a subquery within the XML_attributes_clause to retrieve information from another table into the attributes of an element:
В предыдущей публикации были рассмотрены некоторые приёмы манипуляции с XML в Oracle, теперь рассмотрим как делать выборку данных напрямую в XML. Это на мой взгляд самая интересная часть.
На практике выяснилось, что для Oracle в большинстве случаев не существенно, вернуть ли набор записей или сформированную готовую XML этого набора записей — по времени выполнения эти действия субъективно практически равноценны. Но вот если у вас есть потребность в формировании некоторой XML структуры в приложении на основе данных полученных из БД Oracle, практически наверняка это будет довольно ресурсоемкое мероприятие, гораздо легче переложить этот функционал на базу данных, хотя на первый взгляд и кажется что это не то, чем должен заниматься движок базы данных.
Итак начнем.
Для экспериментов с выборками нам понадобится парочка таблиц, с которыми мы будем проводить эксперименты.
Создадим таблицу с городами:
И создадим таблицу с улицами:
Заполним наши таблицы демонстрационными данными:
Теперь всё готово, приступаем.
1. XMLElement — Выбор XML узла
В самом простом виде выборка в виде XML выглядит так:
Теперь проделаем то же самое с нашей таблицей CITY — выберем все города.
В результате мы получили набор элементов, теперь научимся объединять все эти элементы в один родительский узел, это проще простого.
2. XMLAGG — Объединение (группировка) элементов в родительский узел.
Объединим все наши города из предыдущего примера в один родительский узел country.
Как видим, в результате, мы получили элемент country с вложенным набором элементов city.
Теперь добавим название страны, и узнаем как добавлять атрибуты.
3. XMLATTRIBUTES — Добавление атрибутов в XML элемент.
Начнем с простого, добавим название страны.
Для этого надо просто после названия узла, добавить ещё один параметр XMLATTRIBUTES с перечисленными в скобках параметрами атрибутов.
Вот и всё что нам понадобится для выборки данных в виде XML, теперь попробуем сделать что нибудь более сложное.
Во первых, вынесем наименование города в атрибут, и добавим идентификатор города.
В результате получим XML вида:
Теперь добавим городам дочерние элементы street (улицы).
Для этого нам придётся использовать группировку.
Присоединим таблицу street и сгруппируем по идентификатору и наименованию города.
В результате получим XML вида:
Это пожалуй самое основное, что необходимо знать.
Далее можно развивать и комбинировать разные конструкции для получения в принципе любых XML.
Читайте также: