Как загрузить xml файл в oracle

Обновлено: 07.07.2024

Перенос данных из систему 1 в систему 2 несколько необычным образом.

Предположим, в системе 1 есть некоторые данные, которые нам надо перенести в систему 2. Предположим, они доступны с использованием следующего запроса:

select arg, func from system1.square_test
;
1 1
2 4
3 9
4 16
5 25
6 36
7 49
8 64
9 81
10 100

Чтоб перенести их традиционным способом, нужно записать DDL создания принимающей таблицы (если ее нет) и скрипт DML для всех строк данных. Что-то типа:

create table system2.square_test (arg number, func number);
insert into system2.square_test (arg, func) values (1, 1);
insert into system2.square_test (arg, func) values (2, 4);
.
insert into system2.square_test (arg, func) values (10, 100);

Конечно, этого никто руками делать не будет, надо записать запросы к системным представлениям, формирующие скрипт, но это довольно долго. Кроме того, в реальности вместо простенькой таблицы square_test может быть некий весьма нетривиальный запрос. А ситуация в реальности такая: "Вот тебе данные - взял и ушел, работать надо!"

Как бы сделать по-быстрому? Например, так:

Первые 2 запроса - в системе 1, остальные - в системе 2 Первые 2 запроса - в системе 1, остальные - в системе 2

Оборачиваем данный нам запрос к системе 1 функцией генерации XML:

Забираем, отправляем по почте, по WhatsApp или по Skype - покидаем систему 1 и доставляем XML к системе 2.

Поскольку наш XML на момент переноски - это просто текст, причем текст в общем случае длинный, создадим в системе 2 временную приемную таблицу с одним столбцом (и одной строкой) типа CLOB:

create table clob_tmp (clob_data clob)
;

Загрузим в нее наш XML текст. Но CLOB и XML для Oracle - две вещи разные и нам надо преобразовать CLOB в XML:

select xmltype(clob_data) from clob_tmp
;

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

create table xml_tmp as
select xmltype(clob_data) as xml_data from clob_tmp
;

Всё, данные находятся в системе 2 - теперь их надо извлечь из XML. Выражение получится довольно заковыристое, но, в общем-то, логичное:

Вот наши данные. Теперь мы можем создать приемную таблицу прямо из этой выборки:

Что имеем на выходе:

Для удобства и скорости обработки - иерархию на таблицы на данном этапе не делаем. Достаточно одной плоской.

Итак! Что же мы имеем?
Excel открывает такой XML файл за 5-10 секунд (ругается, что нет схемы, но открывает)
А что делает Oracle? Как я не изощрялся - XMLTABLE, EXTRACT, XMLTYPE. и т.д. - время импорта 7000 строк в таблицу 5-10 минут.

Быстрее ручками сделать через EXCEL csv файлик, и распарсить его в Oracle. (не серьезно конечно) Хотя. если бы не преобразование чиел с плав. точкой в 1,123E * 10^10
Я верю, что БД может сделать это все это быстрее. хотя бы за минуту.

Создаю таблицу, гружу в нее файлик с типом xmltype через pl/sql. Тут вопросов нет-все быстро, все здорово.

В каком моменте задействуется sql loader?
Вытянуть все что надо в нашу плоскую таблицу и занимает минут 7 через sql, через pl/sql минут 15 почему-то. Это и надо ускорять.

вы говорили, что проблема на этапе импорта - это и есть стадия импорта

затем уже обычный select к этой таблице (например, EXTRACTVALUE + XMLSEQUENCE) и все
и то непонятно, откуда 10 минут на таких крошечных объемах. у меня на простеньком сервере 273к строк парсится за 10 секунд

hardhouse
у меня на простеньком сервере 273к строк парсится за 10 секунд

Сколько там полей? Какой первоначальный размер в МБ файла? Сильная иерархия?

dbms_xmldom
навесить внешнюю таблицу препроцессором на xsltproc, который выдает нужный csv.

Какое слово гуглить? :)

План получается адский. Вот запрос:

План стал красивее, но скорости мне не прибавило =( Опять же в SQL парсится 1000 записей за минуту, в PL/SQL блоке за 3 минуты.
. Нужно больше (золота) скорости.

так попробовал, функция не хочет работать с пустым значением иерархии. вот пример
из XML в TABLE

За 2 часа того что я нашел в интернете смог написать такую строку

сломал пол психики =), но получил все равно что-то явно не то.

Посмотри, как при создании таблицы было указано, как хранить поле с XMLTYPE, которое потом парсится, как
XMLTYPE имя_столбца STORE AS CLOB
или
XMLTYPE имя_столбца STORE AS SECUREFILE BINARY XML

Казалось бы, зачем вообще может возникнуть необходимость разбирать XML на стороне БД?

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

Мне совершенно не хотелось бы здесь касаться DOM парсера. Скажу лишь, что он есть, реализуется пакетом DBMS_XMLDOM. Временами он может оказаться крайне полезным разработчику, а разобраться с ним, не составит труда любому, сталкивавшемуся ранее с DOM парсерами от других производителей.

Инновационной особенностью оракла является тип XMLType и средства работы с ним. Этот тип является частью технологии XML DB, которая включена в поставку Oracle Database начиная с версии 9.2.

Исходный текст документа XML может быть передан конструктору XMLType в виде значений типов CLOB, BLOB, VARCHAR2, BFILE. Пожалуй, стоит отметить, что BFILE позволяет загрузить файл с файловой системы сервера — никак не клиента, потому если наш XML находится на стороне клиента и он достаточно велик, чтобы быть переданным в виде строки в запросе, пожалуй, следует озаботиться возможностью доставки файла с XML содержимым на файловую систему сервера.

Пример создания экземпляра XMLType, с содержимым, передаваемым в строке:

Создав экземпляр XMLType, можно попытаться сделать первые робкие шажки для разбора нашего XML. Тип XMLType реализует метод Extract, который, принимая XPatch выражение, возвращает фрагмент XML, сочетающийся с этим выражением. Фрагмент XML (XML Fragment), в отличии от правильно построенного XML (whellformed XML) допускает отсутствие корневого элемента (или же, другими словами, допускающий более одного корневого элемента в своем составе).

Так в примере ниже, три выражения возвращают три фрагмента XML. Первый возвращает все вхождения элемента word, второй возвращает только первое его вхождение, третий возвращает фрагмент текстового содержимого элемента word, для которого значение атрибута seq равняется двум.

Здесь, думаю, стоит поставить жирный акцент на том, что в третьем случае возвращается именно фрагмент XML, никак не значение этого элемента. Различия станут заметны лишь тогда, когда это значение будет содержать подстановочные (escaped) символы, вроде &,>. Для того, чтобы получить значение элемента, следует использовать функцию extractValue. Тут упомяну, что основные методы XMLType, продублированы SQL функциями, или же наоборот, основные функции работы с XMLType реализованы в виде его методов. Однако extractValue — исключение. extractValue представлена только в виде фунации. XMLType, к сожалению, не реализует метод extractValue.

Пожалуй, следует еще упомянуть о правилах работы с пространствами имен. Не всякого интуиция приводит к верному пониманию этих механизмов работы. Функции (и метод) extract, extractValue, как один из параметров принимают описание пространства имен. Описанные в этом параметре пространства имен могут быть использованы в XPath выражении. И именно это я хочу подчеркнуть особо. Обратите внимание на третий случай. Пространства имен в XML и XPatch выражениях имеют разные псевдонимы, но одинаковые URI, потому разбор происходит успешно.

Итак, научившись извлекать значения, теперь следовало бы научиться их разделять. Напомню, в первом случае, для первого примера, мы пытались выбрать все элементы word из XML, и нам это удалось, мы получили два элемента word, однако получили мы их в одном фрагменте. Для того, чтобы представить фрагмент, содержащий несколько корневых элементов в виде последовательности фрагментов, каждый из которых содержит по одному корневому элементу существует конвейерная (pipelined) функция XMLSeqence. Функция возвращает XMLSequenceType, который представляет собой таблицу значений XMLType.

Если вдруг кто запамятовал, напомню, что конвейерные функции возвращают как бы коллекции, потому при вызове оборачиваются выражением table. К результатам этих функций обращаются, используя виртуальный столбец column_value, либо же выражение value(), а потому для табличного выражения(table collection excpression) следует определить псевдоним. Если вдруг кто и не знал этого, рекомендую заучить это как мантру, понимание придет со временем, и то, лишь если понадобится.

Простейший пример использования XMLSequence:

Попытаюсь проговорить что здесь происходит, хоть и опасаюсь, что по-русски это окажется намного более сумбурно, и куда менее понятно, нежели на SQL. В выражении from мы сначала создаем экземпляр XMLType, передавая ему строку, содержащую текст XML. Далее, используя метод extract, мы извлекаем в один фрагмент все элементы b, которые содержит элемент а. Полученный фрагмент XML передается параметром в конвейерную функцию XMLSequence, для вызова которой, согласно правилам грамматики, используется предложение table. Набору записей, описываемым этим предложением, присваивается псевдоним t. В select-list'e мы получаем экземпляр объекта возвращенного табличным выражением t, он у нас имеет тип XMLType. Для каждой строки возвращаемой табличным выражением этот экземпляр содержит один фрагмент элемента b исходного XML. Передаем этот объект в качестве параметра функции extractValue. Результат — на лицо.

На самом деле, все далеко не так сложно, как получается в моем изложении. К этому достаточно лишь малость привыкнуть. Но насилие над мозгом еще не в полной степени завершено. То, что у нас получилось на данном этапе, работает только для одного XML документа. Если у нас исходный текст нескольких XML лежит в табличке и нам нужно разобрать сразу несколько из них, нам придется вспомнить еще что такое левая корреляция (left correlation). Здесь тоже нет ничего военного. Эта штука придумана Ораклом и специально для табличных выражений (table collection expression). Суть сводится к тому, что в табличном выражении могут использоваться значения (столбцы) из наборов данных, определенных в выражении from перед (слева) от самого табличного выражения. На практике это выглядит совсем не так ужасно, как на слух:

Здесь в табличном выражении t используется значение xml таблицы demo3. Выражение будет вычислено для каждой строки таблицы demo3. Это и есть то самое, что называется таким вычурным словом — левая корреляция.

Описанного функционала вполне достаточно, чтобы разобрать XML практически любой сложности. Этими средствами нельзя разобрать, пожалуй, лишь иерархически представленные данные заведомо неизвестной глубины вложенности. Для разбора подобных структур придется прибегнуть к XSLT, чтобы привести XML к более удобочитаемому виду. XSLT преобразование осуществляется функцией XMLTransform, которая в качестве первого параметра принимает XMLType исходного документа, второго XMLType XSL шаблона, а возвращает XMLType результата преобразования.

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

Как видите, здесь нет ничего нового. Все та же левая корреляция. Единственное, на что хотелось бы обратить внимание, на (+) в конце табличного выражения subdtl. Как, наверное, не сложно догадаться, он обозначает, что следует использовать внешнее соединение. Если бы мы его не указали, мы не получили бы строки с detail 3.

Итак, что же предстало пред нашими глазами? Мы имеем один объектный тип, сравнительно ограниченный набор функций, дающий практически не ограниченный набор возможностей. Мне безумно нравится эта реализация. Особенно меня восторгает то, что Oracle corp не пришлось рихтовать семантику их SQL, чтобы вписать в него XML. Все описанные особенности — объекты, конвейерные функции, табличные выражения используются этой технологией, но не созданы специально для нее. Получается, подобную реализацию мог бы воплотить кто угодно. Эта реализация жирной линией подчеркивает мощь и гибкость ораклиного SQL движка.

На этой ноте я мог бы и закончить, однако мне покоя не дает предвосхищаемый мною вопрос, да с упреком. «Дружище, ты в каком веке вообще живешь, ты на календарь давно заглядывал? На дворе близится к концу 2011й год, уже далеко не первый продакшн поднят на 11r2 версии датабазы, а ты все жуешь девятошный функционал». Да, есть такой грешок за мной. Я прекрасно знаю, что в 10й версии ввели чудный XMLTable, полностью задвигающий на задний план только что описанный мною функционал. Позволяющий еще легче и еще более не принужденно разбирать XML. Однако по XMLTable у меня еще не достаточно опыта, чтобы сказать что либо сверх и без того очевидного. Потому ограничусь лишь простой демонстрацией.

Покажу на том же примере:

Казалось бы, букв стало много больше, может возникнуть справедливый вопрос… а в чем же профит инновации? Профит в том, что первым параметром в XMLTable передается уже не XPath выражение, а XQuery. А значит, объединение может быть произведено именно его средствами, а не средствами SQL. XMLTable обещает быть еще той вкуснятиной, но, увы, повторюсь, мне о нем пока нечего рассказать.

Владимир Пржиялковский , преподаватель технологий 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>'));

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:

Убедитесь в этом сами, что 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;

Следующий пример – агрегирующей функции 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, что вызвано чересчур быстрыми темпами развития этих технологий.

Читайте также: