Казалось бы, зачем вообще может возникнуть необходимость разбирать XML на стороне БД?
Но на то может быть много причин, и у каждого они могут быть своими. Некоторых, и меня в том числе, вовсе не гнушает реализация прикладной логики средствами БД, а кому-то это кажется архаичным пережитком и полезность инструментария для работы с XML в СУБД, таким людям может показаться сомнительной. Однако, полагаю, мало кто станет возражать в полезности наличия такой возможности на этапе эксплуатации приложения. К примеру — не приняло у нас приложение прайс-лист оптовика — сумбурно выругалось на отсутствие перекодировки по каким-то позициям. Более 20к позиций в XML — поди там разберись, где собака порылась, что конкретно смутило приложение. Согласитесь, ведь тут здорово было бы иметь возможность представить список товаров, перечисленных в XML в виде набора данных, который можно соединить с перекодировочной таблицей, чтобы выявить одним махом все позиции, не имеющие перекодировки? И подобных примеров может быть приведено множество. Мне доводилось заниматься поддержкой приложения, интегрирующегося с внешними системами посредством обмена XML сообщений, и, не смотря на то, что приложение самостоятельно не использовало предоставляемый Oracle инструментарий, он оказался и весьма кстати мне и моим коллегам при поддержке этого продукта.
В этой статье я хотел бы продемонстрировать на сколько легко и непринужденно можно разобрать 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 обещает быть еще той вкуснятиной, но, увы, повторюсь, мне о нем пока нечего рассказать.
Но на то может быть много причин, и у каждого они могут быть своими. Некоторых, и меня в том числе, вовсе не гнушает реализация прикладной логики средствами БД, а кому-то это кажется архаичным пережитком и полезность инструментария для работы с XML в СУБД, таким людям может показаться сомнительной. Однако, полагаю, мало кто станет возражать в полезности наличия такой возможности на этапе эксплуатации приложения. К примеру — не приняло у нас приложение прайс-лист оптовика — сумбурно выругалось на отсутствие перекодировки по каким-то позициям. Более 20к позиций в XML — поди там разберись, где собака порылась, что конкретно смутило приложение. Согласитесь, ведь тут здорово было бы иметь возможность представить список товаров, перечисленных в XML в виде набора данных, который можно соединить с перекодировочной таблицей, чтобы выявить одним махом все позиции, не имеющие перекодировки? И подобных примеров может быть приведено множество. Мне доводилось заниматься поддержкой приложения, интегрирующегося с внешними системами посредством обмена XML сообщений, и, не смотря на то, что приложение самостоятельно не использовало предоставляемый Oracle инструментарий, он оказался и весьма кстати мне и моим коллегам при поддержке этого продукта.
В этой статье я хотел бы продемонстрировать на сколько легко и непринужденно можно разобрать XML различной степени сложности используя инструментальные средства Oracle Database.
Мне совершенно не хотелось бы здесь касаться DOM парсера. Скажу лишь, что он есть, реализуется пакетом DBMS_XMLDOM. Временами он может оказаться крайне полезным разработчику, а разобраться с ним, не составит труда любому, сталкивавшемуся ранее с DOM парсерами от других производителей.
Инновационной особенностью оракла является тип XMLType и средства работы с ним. Этот тип является частью технологии XML DB, которая включена в поставку Oracle Database начиная с версии 9.2.
Исходный текст документа XML может быть передан конструктору XMLType в виде значений типов CLOB, BLOB, VARCHAR2, BFILE. Пожалуй, стоит отметить, что BFILE позволяет загрузить файл с файловой системы сервера — никак не клиента, потому если наш XML находится на стороне клиента и он достаточно велик, чтобы быть переданным в виде строки в запросе, пожалуй, следует озаботиться возможностью доставки файла с XML содержимым на файловую систему сервера.
Пример создания экземпляра XMLType, с содержимым, передаваемым в строке:
select XMLType(
'<hello-world>
<word seq="1">Hello</word>
<word seq="2">world</word>
</hello-world>
') XML
from dual
Создав экземпляр XMLType, можно попытаться сделать первые робкие шажки для разбора нашего XML. Тип XMLType реализует метод Extract, который, принимая XPatch выражение, возвращает фрагмент XML, сочетающийся с этим выражением. Фрагмент XML (XML Fragment), в отличии от правильно построенного XML (whellformed XML) допускает отсутствие корневого элемента (или же, другими словами, допускающий более одного корневого элемента в своем составе).
Так в примере ниже, три выражения возвращают три фрагмента XML. Первый возвращает все вхождения элемента word, второй возвращает только первое его вхождение, третий возвращает фрагмент текстового содержимого элемента word, для которого значение атрибута seq равняется двум.
SQL> with demo1 as (
2 select XMLType(
3 '<hello-world>
4 <word seq="1">Hello</word>
5 <word seq="2">world</word>
6 </hello-world>
7 ') xml
8 from dual
9 )
10 select t.xml.extract('//word') case1
11 ,t.xml.extract('//word[position()=1]') case2
12 ,t.xml.extract('//word[@seq=2]/text()') case3
13 from demo1 t;
CASE1 CASE2 CASE3
--------------------------- -------------------------- -------
<word seq="1">Hello</word> <word seq="1">Hello</word> world
<word seq="2">world</word>
Здесь, думаю, стоит поставить жирный акцент на том, что в третьем случае возвращается именно фрагмент XML, никак не значение этого элемента. Различия станут заметны лишь тогда, когда это значение будет содержать подстановочные (escaped) символы, вроде &,>. Для того, чтобы получить значение элемента, следует использовать функцию extractValue. Тут упомяну, что основные методы XMLType, продублированы SQL функциями, или же наоборот, основные функции работы с XMLType реализованы в виде его методов. Однако extractValue — исключение. extractValue представлена только в виде фунации. XMLType, к сожалению, не реализует метод extractValue.
SQL> with demo2 as (select xmltype('<a><&hello&></a>') xml from dual)
2 select t.xml.extract('a/text()').getStringVal() case1
3 ,extractValue(t.xml,'a') case2
4 from demo2 t;
CASE1 CASE2
--------------------------- --------------------------
<&hello&> <&hello&>
Пожалуй, следует еще упомянуть о правилах работы с пространствами имен. Не всякого интуиция приводит к верному пониманию этих механизмов работы. Функции (и метод) extract, extractValue, как один из параметров принимают описание пространства имен. Описанные в этом параметре пространства имен могут быть использованы в XPath выражении. И именно это я хочу подчеркнуть особо. Обратите внимание на третий случай. Пространства имен в XML и XPatch выражениях имеют разные псевдонимы, но одинаковые URI, потому разбор происходит успешно.
SQL> select extractValue(t.xml,'a') case1
2 ,extractValue(t.xml,'a','xmlns="foo"') case2
3 ,extractValue(t.xml,'y:a/@z:val','xmlns:y="foo" xmlns:z="bar"') case3
4 from (select XMLType('<a xmlns="foo" xmlns:x="bar" x:val="a-val">a-text</a>') XMl from dual) t;
CASE1 CASE2 CASE3
--------------------------- -------------------------- --------------------------
a-text a-val
Итак, научившись извлекать значения, теперь следовало бы научиться их разделять. Напомню, в первом случае, для первого примера, мы пытались выбрать все элементы word из XML, и нам это удалось, мы получили два элемента word, однако получили мы их в одном фрагменте. Для того, чтобы представить фрагмент, содержащий несколько корневых элементов в виде последовательности фрагментов, каждый из которых содержит по одному корневому элементу существует конвейерная (pipelined) функция XMLSeqence. Функция возвращает XMLSequenceType, который представляет собой таблицу значений XMLType.
Если вдруг кто запамятовал, напомню, что конвейерные функции возвращают как бы коллекции, потому при вызове оборачиваются выражением table. К результатам этих функций обращаются, используя виртуальный столбец column_value, либо же выражение value(), а потому для табличного выражения(table collection excpression) следует определить псевдоним. Если вдруг кто и не знал этого, рекомендую заучить это как мантру, понимание придет со временем, и то, лишь если понадобится.
Простейший пример использования XMLSequence:
SQL> select extractValue(value(t),'b') result
2 from table(XMLSequence(XMLType('<a><b>b1</b><b>b2</b></a>').extract('a/b'))) t;
RESULT
-------------------------------------------------------------------------------------
b1
b2
Попытаюсь проговорить что здесь происходит, хоть и опасаюсь, что по-русски это окажется намного более сумбурно, и куда менее понятно, нежели на 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 перед (слева) от самого табличного выражения. На практике это выглядит совсем не так ужасно, как на слух:
SQL> with demo3 as(select 1 id, XMLType('<a><b>b1</b><b>b2</b></a>') xml from dual
2 union all select 2 id, XMLType('<a><b>b3</b><b>b4</b></a>') xml from dual)
3 select id xml_id
4 ,extractValue(value(t),'b') result
5 from demo3 s,table(XMLSequence(s.xml.extract('a/b'))) t;
XML_ID RESULT
---------- --------------------------------------------------
1 b1
1 b2
2 b3
2 b4
Здесь в табличном выражении t используется значение xml таблицы demo3. Выражение будет вычислено для каждой строки таблицы demo3. Это и есть то самое, что называется таким вычурным словом — левая корреляция.
Описанного функционала вполне достаточно, чтобы разобрать XML практически любой сложности. Этими средствами нельзя разобрать, пожалуй, лишь иерархически представленные данные заведомо неизвестной глубины вложенности. Для разбора подобных структур придется прибегнуть к XSLT, чтобы привести XML к более удобочитаемому виду. XSLT преобразование осуществляется функцией XMLTransform, которая в качестве первого параметра принимает XMLType исходного документа, второго XMLType XSL шаблона, а возвращает XMLType результата преобразования.
В принципе, на этом с теорией можно и закончить. В завершение лишь продемонстрирую пример извлечения элементов с разных уровней вложенностей XML. У новичков это, порой, вызывает сложности.
SQL> with demo4 as(
2 select XMLType(
3 '<master>
4 <id>mater id</id>
5 <details>
6 <detail>
7 <id>detail 1 id</id>
8 <sub_details>
9 <sub_detail>
10 <id>sub_detail 1.1 id</id>
11 </sub_detail>
12 <sub_detail>
13 <id>sub_detail 1.2 id</id>
14 </sub_detail>
15 </sub_details>
16 </detail>
17 <detail>
18 <id>detail 2 id</id>
19 <sub_details>
20 <sub_detail>
21 <id>sub_detail 2.1 id</id>
22 </sub_detail>
23 <sub_detail>
24 <id>sub_detail 2.2 id</id>
25 </sub_detail>
26 </sub_details>
27 </detail>
28 <detail>
29 <id>detail 3 id</id>
30 </detail>
31 </details>
32 </master>'
33 ) xml from dual)
34 select extractValue(s.xml,'master/id') master_id
35 ,extractValue(value(dtl),'detail/id') detail_id
36 ,extractValue(value(subdtl),'sub_detail/id') sub_detail_id
37 from demo4 s
38 ,table(XMLSequence(s.xml.extract('master/details/detail'))) dtl
39 ,table(XMLSequence(value(dtl).extract('detail/sub_details/sub_detail')))(+) subdtl;
MASTER_ID DETAIL_ID SUB_DETAIL_ID
--------------------------- -------------------------- --------------------------
mater id detail 1 id sub_detail 1.1 id
mater id detail 1 id sub_detail 1.2 id
mater id detail 2 id sub_detail 2.1 id
mater id detail 2 id sub_detail 2.2 id
mater id detail 3 id
Как видите, здесь нет ничего нового. Все та же левая корреляция. Единственное, на что хотелось бы обратить внимание, на (+) в конце табличного выражения subdtl. Как, наверное, не сложно догадаться, он обозначает, что следует использовать внешнее соединение. Если бы мы его не указали, мы не получили бы строки с detail 3.
Итак, что же предстало пред нашими глазами? Мы имеем один объектный тип, сравнительно ограниченный набор функций, дающий практически не ограниченный набор возможностей. Мне безумно нравится эта реализация. Особенно меня восторгает то, что Oracle corp не пришлось рихтовать семантику их SQL, чтобы вписать в него XML. Все описанные особенности — объекты, конвейерные функции, табличные выражения используются этой технологией, но не созданы специально для нее. Получается, подобную реализацию мог бы воплотить кто угодно. Эта реализация жирной линией подчеркивает мощь и гибкость ораклиного SQL движка.
На этой ноте я мог бы и закончить, однако мне покоя не дает предвосхищаемый мною вопрос, да с упреком. «Дружище, ты в каком веке вообще живешь, ты на календарь давно заглядывал? На дворе близится к концу 2011й год, уже далеко не первый продакшн поднят на 11r2 версии датабазы, а ты все жуешь девятошный функционал». Да, есть такой грешок за мной. Я прекрасно знаю, что в 10й версии ввели чудный XMLTable, полностью задвигающий на задний план только что описанный мною функционал. Позволяющий еще легче и еще более не принужденно разбирать XML. Однако по XMLTable у меня еще не достаточно опыта, чтобы сказать что либо сверх и без того очевидного. Потому ограничусь лишь простой демонстрацией.
Покажу на том же примере:
34 select master_id
35 ,details_id
36 ,sub_details_id
37 from demo4 s
38 ,XMLTable('master'
39 passing (s.xml)
40 columns master_id varchar2 (20) path 'id'
41 ,details XMLType path 'details/detail') mstr
42 ,XMLTable('detail'
43 passing (mstr.details)
44 columns details_id varchar2 (20) path 'id'
45 ,sub_details XMLType path 'sub_details/sub_detail')(+) dtl
46 ,XMLTable('sub_detail'
47 passing (dtl.sub_details)
48 columns sub_details_id varchar2 (20) path 'id')(+) sub_dtl;
MASTER_ID DETAILS_ID SUB_DETAILS_ID
--------------------------- -------------------- --------------------
mater id detail 1 id sub_detail 1.1 id
mater id detail 1 id sub_detail 1.2 id
mater id detail 2 id sub_detail 2.1 id
mater id detail 2 id sub_detail 2.2 id
mater id detail 3 id
Казалось бы, букв стало много больше, может возникнуть справедливый вопрос… а в чем же профит инновации? Профит в том, что первым параметром в XMLTable передается уже не XPath выражение, а XQuery. А значит, объединение может быть произведено именно его средствами, а не средствами SQL. XMLTable обещает быть еще той вкуснятиной, но, увы, повторюсь, мне о нем пока нечего рассказать.