Oracle создание xml из запроса
создании данного материала неоценимую помощь мне оказал мой коллега
Доброквашин Михаил.
Для примеров может быть использован сервер Oracle не ниже 9 ой версии, с поддержкой XML TYPE.
Рассмотрим ситуацию, когда нам необходимо с помощью средств oracle разработать приложения для взаимодействия с внешним Веб-сервисом с использованием SOAP протоколу.
Общая схема работы приложения
Итак, наша система работает по следующему алгоритму:
Приложение СУБД Oracle формирует XML документ заданного формата
Приложение СУБД Oracle внешнему веб-сервису сформированный XML
Ожидает ответа со стороны веб - сервиса и
Трансформирует ответ из XML в курсор Oracle и передает его далее во внешнее приложение
Формирование XML
Рассмотрим различные методы для формирования XML в Oracle
Преобразование данных из строки в формат XML:
Для создания XML создается соответствующий XML в формате VARCHAR2
Затем переменная VARCHAR2 преобразуется в CLOB , далее происходит непосредственно преобразование в XML.
Подобное преобразование необходимо для того что тип varchar2 ограничивается 32 килобайтами, а предаваемые xml могут большего объема.
Итак, при работе с XML oracle в процессе преобразований целесообразно использовать тип CLOB.
Стандартные методы работы с XML в Oracle
Сохраняем XML в базу данных
Сохранение XML происходит в поле CLOB
Создание XML
Создание XML в ORACLE удобнее всего преобразованием символьной переменной следующим образом:
итак , мы получаем готовый xml
Расшифровка XML ответа
После того как внешнее приложение или веб сервис вернул нам ответ нам необходимо преобразовать его к курсору или единичному значению.
Посмотрим, как это сделать:
Для преобразования XML используется тип XMLSEQUENCE и команда EXTRACT
Единичное значение
Обработка курсора внешним приложением
Передача данных внешнему приложению
Url - адрес вызова
aXML - сформированный XML документ
Передача данных внешнему приложению осуществляется следующим образом
-- создаем временный LOB
-- отравляем пакет на веб сервер по частям, в 2000 байт
Казалось бы, зачем вообще может возникнуть необходимость разбирать 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 обещает быть еще той вкуснятиной, но, увы, повторюсь, мне о нем пока нечего рассказать.
В любой типичной организации информация хранится в нескольких форматах, часть которой, возможно, помещается в реляционные базы данных, но большая часть все-таки хранится за пределами этих баз данных. Не помещаемая в базы данных информация может храниться в формате конкретных приложений, например, электронных таблиц Excel. Сохранение ее вместо этого в формате XML позволяет упрощать процесс получения доступа и обновления неструктурированных данных организации.
Oracle XML DB (XML-база данных Oracle) не является базой данных специально предназначенного для XML-данных типа. Она просто дополнительно содержит набор встроенных технологий для хранения, извлечения и манипулирования XML-данными. Она обладает преимуществами одновременно и технологии реляционных баз данных, и технологии XML. Например, одна из главных проблем при работе с XML-данными в реляционной базе данных состоит в том, что большинство XML-данных имеют иерархическую структуру по своей природе, в то время как база данных Oracle основана на реляционной модели. Через Oracle XML DB предоставляется возможность эффективной обработки иерархических XML-данных за счет применения специальных SQL-операторов и методов, которые позволяют запрашивать и обновлять XML-данные в базе Oracle. Посредством Oracle XML DB в ядро Oracle встраивается объектная модель документов XML ( Document Object Model — DOM). Благодаря этому, большинство операций с данными XML воспринимается как часть обычного процесса обработки данных.
Oracle XML DB предоставляет возможность просматривать как структурированную, так и неструктурированную информацию в формате реляционных данных. То есть данные можно просматривать как в виде строк в таблице, так и в виде узлов в XML-документе.
Ниже приведен краткий перечень предлагаемых XML-базой данных Oracle преимуществ:
- К XML-данным можно получать доступ с помощью обычных SQL-запросов.
- Для обработки XML-данных можно использовать поддерживаемые Oracle технологии оперативной обработки транзакций (OLTP), создания хранилищ данных, тестирования, пространственных данных и мультимедиа.
- Можно генерировать XML из SQL-запросов Oracle.
- Можно легко преобразовывать XML в формат HTML.
Сохранение XML-данных в Oracle XML DB
Для сохранения и управления XML-данными в реляционной таблице применяется специальный тип данных XMLType. Этот тип, а также тип XDBURIType, который представляет собой еще один встроенный тип, предназначенный для работы с данными XML, позволяет возлагать ответственность за выполнение синтаксического анализа, хранение и извлечение XML-данных на базу данных Oracle. Использовать тип XMLType можно точно так же, как и обычные типы данных в базе данных Oracle. Благодаря ему, правильно оформленный XML-документ теперь можно сохранять в базе данных в виде XML-теста с использованием базового типа данных CLOB.
Ниже приведен пример применения типа данных XMLType:
Тип данных XMLType поставляется с набором специальных XML-методов, которые можно использовать для работы с объектами XMLType. Эти методы можно применять для выполнения как типичных операций в базе данных, наподобие проверки на предмет существования узла или извлечения узла, так и нескольких специальных операций, позволяющих получать доступ к XML-данными и манипулировать ими в виде части обычного SQL-оператора. Эти специальные операции являются отражением развивающегося стандарта SQL/XML. За счет использования уже хорошо известной нотации XPath, они осуществляют обход по структурам XML и отыскивают тот узел или узлы, к которым им следует применить указанные SQL-операторы. Ниже перечислены некоторые наиболее важные операции SQL/XML.
- Extract(). Извлекает подмножество узлов, содержащихся в XMLType.
- ExistsNode(). Проверяет, существует ли конкретный узел в XMLType.
- Validating(). Проверяет правильность содержимого XMLType по схеме XML.
- Transform(). Выполняет XSL-преобразование.
- ExtractValue(). Возвращает узел, соответствующий выражению XPath.
XML-данные имеют абстрактную форму по сравнению с записями в обычных реляционных таблицах. Для оптимизации и выполнения операторов, подразумевающих обработку XML-данных, в Oracle применяется механизм перезаписи запросов, преобразующий выражение XPath в обычный эквивалентный оператор SQL. После этого оптимизатор обрабатывает этот преобразованный SQL-оператор подобно любому другому оператору SQL.
Сохранять XML-данные в XML-базе данных Oracle можно двумя способами.
- С использованием SQL или PL/SQL для вставки данных. Такой подход подразумевает сначала преобразование исходных данных в экземпляр XMLType с помощью конструкторов XMLType.
- С использованием репозитория Oracle XML DB для сохранения XML-данных.
Ниже приведен простой пример, в котором для демонстрации выполнения основанной на SQL операции DML в отношении поддерживающей XML таблицы используется таблица sales_catalog_table. В листинге 2 в эту таблицу вставляется XML-документ.
Теперь для просмотра XML-документа в его исходном формате достаточно просто запросить столбец sales_order таблицы sales_catalog_table, как показано в листинге 3.
После создания из таблицы sales_catalog_table можно очень легко извлекать данные с помощью любого из описанных выше методов. Ниже показан пример применения метода extract(). В этом примере выражения XPath и операции SQL/XML extractValue и existsNode применяются для поиска названия того отправления, у которого в узле SalesOrder/SalesLocation/text() содержится значение Dallas.
Репозиторий Oracle XML DB
Обрабатывать XML-документы в Oracle XML DB лучше всего, сначала загрузив их в специальное хранилище, называемое репозиторием Oracle XML DB. Этот репозиторий имеет иерархическую структуру, как и большинство XML-данных, и позволяет легко запрашивать XML-данные. Для представления отношений между XML-данными в нем используются пути и URL-адреса, а для прохода по содержащимся внутри него папкам и путей — специальный иерархический индекс. В нем могут храниться и данные, отличные от XML, наподобие изображений JPEG, документов Word и многое другое.
Настройка схемы XML
Прежде чем приступить к использованию Oracle XML DB для работы с XML-документами, понадобится выполнить следующие задачи.
Создание реляционного представления из XML-документа
Даже если разработчик плохо разбирается в XML, он все равно может использовать XML-документы, хранящиеся в базе данных Oracle, за счет создания на их основе реляционных представлений. Ниже приведен пример отображения узлов в XML-документе на столбцы в реляционном представлении по имени salesorder_view:
Далее к этому представлению salesorder_view можно выполнять запросы точно так же, как и к любому другому представлению в базе данных Oracle, как показано ниже:
Перенос данных из систему 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. Выражение получится довольно заковыристое, но, в общем-то, логичное:
Вот наши данные. Теперь мы можем создать приемную таблицу прямо из этой выборки:
Читайте также: