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);


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 елементах .


Використання функції SQL EXTRACTVALUE, в свою чергу, залишає можливість відбору не більше одного елемента XML для формування кожного рядка результату SELECT, але зате безболісно прибирає обрамляють значення елемента XML мітки:


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


Те ж саме можна записати простіше, що вже демонструвалося на початку статті.


Перетворення з табличної форми в XMLTYPE


Для зворотного перетворення зручно скористатися функціями, об’єднаними в стандарті SQL: 2003 назвою SQL / XML. У версії 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, що викликано надто швидкими темпами розвитку цих технологій.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*