Хотя гибридная архитектура DB2 обнаруживает значительные отличия от предыдущих версий, использование новых инструментов для работы с XML не должно вызывать трудностей. Если вы уже хорошо разбираетесь в SQL, то можете сразу же применить свои навыки для работы с XML-данными, которые хранятся в DB2 с характерными для этого формата особенностями. О способах выполнения читайте в нашей статье.
Инструменты для работы с XML-данными DB2 Viper включают обновленную поддержку управления хранилищами, индексации и языка запросов. Мы выясним, как создавать и выполнять запросы на выборку данных, хранимых в XML-столбцах, при помощи «чистого» SQL и SQL c расширениями XML (SQL/XML). В одной из следующих статей предполагается изучить поддержку языка XQuery, развивающегося отраслевого стандарта, в среде DB2, и подробно исследовать, в каких случаях использование этого языка будет наиболее полезным.
Возможно, для вас будет неожиданной информация о том, что DB2 поддерживает двуязычные запросы, то есть запросы, в которых сочетаются выражения SQL и XQuery. Выбор используемого языка (или сочетания языков) зависит как от требований прикладной системы, так и от ваших навыков. Сочетание элементов двух языков в одном запросе не такая сложная задача, как можно подумать, а данный метод может предложить вам более сильные средства для поиска и объединения XML и традиционных SQL-данных.
База данных для упражнений
Запросы, описанные в этой статье, обращаются к примерам таблиц, создание которых описано в статье "Что нового в DB2 Viper". Коротко: примеры таблиц "items" и "clients" определены в листинге 1:
create table items ( id int primary key not null, brandname varchar(30), itemname varchar(30), sku int, srp decimal(7,2), comments xml ) create table clients( id int primary key not null, name varchar(50), status varchar(10), contactinfo xml ) |
Примеры XML-данных, включенных в столбец "items comments", показаны на рисунке 1, а примеры XML- данных, включенных в столбец "clients contactinfo" - на рисунке 2. Последующие примеры запросов будут ссылаться на конкретные элементы одного или сразу обоих документов XML.
Рисунок 1. Пример документа XML сохранен в столбце "comments" таблицы "items"
Рисунок 2. Пример документа XML сохранен в столбце "contactinfo" таблицы "clients"
Среда запроса
Все запросы в этой статье разработаны для того, чтобы вы могли повторить их на своем компьютере; это можно сделать либо через обработчик командной строки DB2, либо через редактор команд DB2 Command Editor модуля DB2 Control Center. Изображения снимков экрана и инструкции в данной статье относятся ко второму варианту (DB2 Viper поставляется со средой разработчика Developer Workbench на базе платформы Eclipse, что может помочь программисту конструировать запросы наглядным способом. В этой статье проблемы разработки приложений в среде разработки Developer Workbench не рассматриваются).
Для использования редактора команд DB2 Command Editor, откройте Control Center и выберите команду Tools > Command Editor. На экране появится окно, показанное на рисунке 3 . В верхней панели введите запрос, затем нажмите зеленую стрелку в верхнем левом углу, чтобы выполнить его, и просмотрите полученные результаты в нижней панели или на отдельной вкладке "Query Results".
Рисунок 3. Окно DB2 Command Editor, которое может быть вызвано из DB2 Control Center 
Запросы с использованием только языка SQL
Даже если ваши знания языка SQL не слишком обширны, вы можете, затратив некоторые усилия, создать запрос на выборку XML-данных. Например, следующим запросом осуществляется выборка всего содержимого таблицы "clients", включая XML-информацию, хранимую в столбце "contactinfo":
Листинг 2. Простое предложение SELECT
select * from clients |
Конечно, вы можете писать более избирательные запросы SQL, содержащие операторы реляционной проекции и ограничения. Представленный в следующем листинге запрос осуществляет выборку идентификаторов, фамилий и контактной информации для всех клиентов, имеющих статус "Gold". Обратите внимание, что столбец "contactinfo" содержит XML-данные, а остальные два столбца - нет.
Листинг 3. Простое предложение SELECT c проекцией и ограничением
select id, name, contactinfo from clients where status = 'Gold' |
И, как можно было бы предположить, есть возможность создавать представления на основе запросов, что демонстрируется в следующем листинге на примере представления "godview".
Листинг 4. Создание представления, содержащего столбец XML
create view goldview as select id, name, contactinfo from clients where status = 'Gold' |
К сожалению, существует несколько ситуаций, в которых вы не сможете обойтись одним SQL. «Чистое» предложение SQL позволяет вам запросить XML-документ полностью (как вы только что убедились), но вы не можете определить предикаты запроса для XML, запросить часть XML-документа или определить значения элементов из какого-либо XML-документа. Другими словами, вы не можете выполнить операции ограничения, объединения, группировки или упорядочения фрагментов XML-документов при помощи «чистого» SQL. Например, невозможно получить только адреса электронной почты ваших клиентов со статусом "Gold" или фамилии клиентов, имеющих почтовый индекс "95116". Чтобы выполнить эти виды запросов, вам необходимо использовать SQL с расширениями XML (SQL/XML), XQuery или их сочетание.
В следующем разделе рассматриваются несколько основных средств SQL/XML, а в одной из следующих статей мы изучим, как писать запросы XQuery и комбинировать XQuery c SQL.
Запросы с использованием SQL/XML
Как видно из названия SQL/XML, данный язык был разработан, чтобы объединить сферы применения SQL и XML Он развивался как часть разработки стандарта SQL, и теперь включает спецификации для встраивания выражений XQuery и XPath в предложения SQL. XPath - это язык для навигации по документам XML с целью поиска элементов или атрибутов. Язык XQuery включает поддержку XPath.
Важно отметить, что выражения XQuery (и XPath) являются регистрозависимыми. Например, запрос XQuery, который ссылается на элемент "zip", не будет применен к элементам XML, с именами "ZIP" или "Zip". Программисты SQL часто забывают о регистрозависимости языка, поскольку синтаксис запроса SQL позволяет использовать "zip", "ZIP" и "Zip" для ссылки на название одного и того же столбца.
Программа DB2 Viper предоставляет более 15 функций SQL/XML, что позволяет осуществлять поиск конкретных данных в документах XML, преобразовывать реляционные данные в XML, и данные XML в реляционные данные, а также выполнять другие полезные задачи. Эта статья не охватывает в полной мере возможности языка SQL/XML, но мы рассматриваем некоторые распространенные задачи запросов и основные функции SQL/XML, которые позволяют решить эти задачи.
"Ограничение" результатов по значениям элементов XML
Программисты SQL часто пишут запросы, ограничивающие возвращаемые из СУБД записи на основании некоторого условия. Например, запрос SQL, показанный в листинге 3 ограничивает возвращение записей, выбранных из таблицы "clients", таким образом, чтобы результаты включали только клиентов, имеющих статус "Gold". В этом случае статус клиента фиксируется в столбце SQL-типа VARCHAR. Но как поступить, если необходимо ограничить поиск на основании некоторого условия, которое следует применить к данным в столбце XML? Один из способов выполнить эту задачу заключается в использовании функции SQL/XML XMLExists.
Функция XMLExists позволяет перейти к элементу внутри документа XML и проверить, выполняется ли конкретное условие. Если функция XMLExists используется в составе выражения WHERE, она ограничивает возвращаемые результаты теми записями, которые содержат XML-документ с конкретными значениями элемента XML (другими словами, когда определенные значения равны "true").
Давайте для примера рассмотрим решение поставленной ранее задачи. Представьте, что вам нужно найти фамилии всех клиентов, имеющих определенный почтовый код. Как вы помните, таблица "clients" хранит адреса клиентов (включая почтовые индексы) в столбце XML (См. рисунок 2.) При помощи данной функции вы можете выполнить поиск по столбцу XML с критерием "значение почтового индекса" и соответственно ограничить возвращаемые результаты.. Запрос SQL/XML, показанный в следующем листинге, возвращает фамилии клиентов, имеющих почтовый индекс 95116.
Листинг 5. Ограничение результатов поиска на основании значения элемента XML
select name from clients
where xmlexists('$c/Client/Address[zip="95116"]'
passing clients.contactinfo as "c")
|
Первая строка предложения SQL определяет, что вы хотите получить только информацию из столбца "name" таблицы "clients". Предложение WHERE вызывает функцию XMLExists, определяющую выражение XPath, которое заставляет DB2 перейти к элементу "zip" в поисках значения 95116. Предложение "$c/Client/Address" обозначает путь в иерархии XML-документа, по которому DB2 может найти элемент "zip". Используя данные, доступные с узла "$c" (мы скоро объясним, о чем идет речь), DB2 переходит через элемент "Client" к вложенному в него элементу "Address", чтобы проверить почтовый индекс (значение "zip"). Заключительная строка вычисляет значение "$c": это столбец "contactinfo" таблицы "clients". Таким образом, DB2 проверяет XML-данные, содержащиеся в столбце "contactinfo", переходя от корневого элемента "Client" к полю "Address", а затем к полю "zip", и определяет, имеет ли клиент указанный почтовый индекс. Если имеет, то функция XMLExists равна "true", и DB2 возвращает фамилию клиента, связанного с этой записью.
Распространенная ошибка влечет за собой формулирование предиката XMLExists, что показано в листинге 6.
Листинг 6. Ограничение результатов поиска на основании значения элемента XML
select name from clients
where xmlexists('$c/Client/Address[zip="95116"]'
passing clients.contactinfo as "c")
|
Если этот запрос будет успешно выполнен, он не ограничит результаты поиска кругом клиентов, имеющих почтовый индекс 95116 (причина ошибки - в семантике языка, определенной в стандарте; это не является уникальным именно для DB2). Чтобы ограничить результаты поиска кругом клиентов, имеющих почтовый индекс 95116, необходимо использовать синтаксис, показанный ранее в листинге 5.
Возможно, вас интересует, как включить запрос, ограничивающий результаты поиска XML-данных, в прикладную программу. Хотя в этой статье подробно не рассматривается тема разработки приложений, мы включили в нее простой пример на языке Java, который использует маркер параметра в предложении SQL/XML для ограничения вывода информацией только о тех клиентах, которые имеют данный почтовый индекс.
"Проецирование" значения элемента XML
Теперь давайте рассмотрим слегка отличающуюся ситуацию, в которой вы предполагаете спроецировать значения XML на возвращаемое результирующее множество. Другими словами, требуется получить одно или более значений элементов из наших XML-документов. Существует несколько способов решить эту задачу. Давайте сначала воспользуемся функцией XMLQuery для получения значения одного элемента, а затем используем функцию XMLTable, чтобы получить значения нескольких элементов и отобразить их на столбцы результирующего множества SQL.
Рассмотрим решение сформулированной ранее задачи: как создать отчет, содержащий список адресов электронной почты клиентов со статусом "Gold". Запрос, представленный в следующем листинге 7 , для решения этой задачи вызывает функцию XMLQuery:
select xmlquery('$c/Client/email'
passing contactinfo as "c")
from clients
where status = 'Gold'
|
В первой строке определяется, что вы хотите возвратить значения для вложенного элемента "email" корневого элемента "Client". Вторая и третья строки указывают, где DB2 может найти эту информацию - в столбце "contactinfo" таблицы "clients". Четвертая строка продолжает ограничивать запрос, указывая, что вас интересуют только адреса электронной почты клиентов со статусом "Gold". Этот запрос возвратит набор элементов XML и их значений. Например, если у вас есть 500 клиентов со статусом "Gold", причем каждый из них имеет один адрес электронной почты, вывод запроса будет представлять собой результирующее множество в виде одного столбца с 500 записями, как показано в листинге 8:
Листинг 8. Пример вывода данных для предыдущего запроса
1 -------------------------------------------- <email>user5976@anyprovider.com</email> . . . <email>someID@yahoo.com</email> |
Если у вас имеется несколько адресов электронной почты для некоторых клиентов со статусом "Gold", то, возможно, вы захотите указать DB2, что нужно возвратить только основной адрес (то есть, первый из адресов электронной почты, найденный в документе "contactinfo" для данного клиента). Для этого вы можете изменить выражение XPath в первой строке запроса:
select xmlquery('$c/Client/email[1]'
passing contactinfo as "c")
from clients
where status = 'Gold'
|
Наконец, если у вас нет адресов электронной почты для некоторых клиентов со статусом Gold, вам, возможно, захочется написать запрос, в результирующее множество которого включены нулевые значения. Чтобы получить этот результат, измените предыдущий запрос, добавив другой предикат в предложение WHERE, который будет проверять документы на отсутствие информации об адресе электронной почты. Вы уже хорошо знаете функцию SQL/XML, которая позволяет решить задачу - это функция XMLExists. Листинг 10 демонстрирует, как можно переписать предыдущий запрос, чтобы отобрать любые записи для клиентов со статусом "Gold", в контактной информации которых (хранимой в формате XML), отсутствует адрес электронной почты.
select xmlquery('$c/Client/email[1]'
passing contactinfo as "c")
from clients
where status = 'Gold'
and xmlexists('$c/Client/email' passing contactinfo as "c")
|
Теперь давайте рассмотрим несколько отличающуюся ситуацию, где вам нужно получить несколько значений элемента XML. Функция XMLTable генерирует табличный вывод из данных, хранимых в столбцах XML, и оказывается весьма полезной для обеспечения программистов "реляционным" представлением XML-данных. Подобно функциям XMLExist и XMLQuery, XMLTable заставляет DB2 переходить по иерархии документа XML, чтобы найти запрашиваемые данные. Но функция XMLTable также включает предложения для отображения объектных XML-данных на столбцы результирующего множества с типом данных SQL.
Рассмотрим следующий запрос (листинг 11), отображающий столбцы, содержащие и реляционные, и XML-данные, хранимые в таблице "items" (таблица "items" изображена на рисунке 1 .) Идентификатор комментария, идентификатор клиента и сообщения хранятся в XML-документах в столбце "comments". Названия объектов хранятся в столбце SQL, тип данных VARCHAR (строка).
select t.Comment#, i.itemname, t.CustomerID, Message from items i,
xmltable('$c/Comments/Comment' passing i.comments as "c"
columns Comment# integer path 'CommentID',
CustomerID integer path 'CustomerID',
Message varchar(100) path 'Message') as t
|
Первая строка определяет столбцы, которые следует включить в результирующее множество. Столбцы, заключенные в кавычки с префиксом в виде переменной "t", создаются на основании значений элементов XML, в соответствии с указаниями последующих строк запроса. Вторая строка вызывает функцию XMLTable для указания DB2 столбца XML, содержащего объектные данные ("comments") и путь внутри документа XML, который ведет к искомым элементам (во вложенном элементе "Comment" корневого элемента "Comments"). Предложение "columns", объединяющее строки с 3 по 5, определяет конкретные элементы XML, которые будут отображены на столбцы вывода в результирующем множестве SQL, определенном в строке 1. Часть этого отображения включает определение типа данных, в который будут преобразованы значения элементов XML. В данном примере все XML-данные переведены в традиционные типы данных SQL.
На рисунке 4 показаны примеры результатов, полученных при выполнении этого запроса. Как мы видим, вывод представляет собой простое результирующее множество SQL. Обратите внимание - имена столбцов переведены в верхний регистр, что является обычным для SQL.
Рисунок 4. Пример вывода запроса, использующего функцию XMLTable
При желании вы можете использовать функцию XMLTable для создания результирующего множества, которое включает и столбцы XML. Например, следующее предложение генерирует такое же результирующее множество, как и в предыдущем примере, за исключением того, что данные о "Сообщениях" содержаться в столбце XML, а не в столбце SQL VARCHAR.
select t.Comment#, i.itemname, t.CustomerID, Message from items i,
xmltable('$c/Comments/Comment' passing i.comments as "c"
columns Comment# integer path 'CommentID',
CustomerID integer path 'CustomerID',
Message XML by ref path 'Message') as t
|
Создание реляционных представлений данных XML
Легко догадаться, что функции SQL/XML могут использоваться для определения представлений. Это особенно полезно, если вы хотели бы обеспечить программистов приложений на языке SQL реляционной моделью ваших данных, хранимых в формате XML.
Создание реляционного представления на основе данных в столбце XML ненамного сложнее, чем отображение значений элементов XML. Вы просто пишете предложение SQL/XML SELECT, которое вызывает функцию XMLTable, и используете его как основу для определения представления. Следующий пример (листинг 13) создает представление на основе информации в XML и не-XML столбцах таблицы "items" (точно так же, как в запросе, представленном в листинге 11.)
Листинг 13. Создание представления на основе вывода функции XMLTable
create view commentview(itemID, itemname, commentID, message, mustrespond) as
select i.id, i.itemname, t.CommentID, t.Message, t.ResponseRequested from items i,
xmltable('$c/Comments/Comment' passing i.comments as "c"
columns CommentID integer path 'CommentID',
Message varchar(100) path 'Message',
ResponseRequested varchar(100) path 'ResponseRequested') as t;
|
Хотя создание реляционных представлений на основе данных XML-столбцов не представляет особой сложности, все же следует пользоваться этой функцией с осторожностью. DB2 не использует индексы XML-столбцов, когда по таким представлениям создаются запросы. Так, если бы вы проиндексировали элемент ResponseRequest и создали запрос SQL, ограничивающий результаты тех столбцов, с которых следует получить ответы, определенным значением, программа DB2 просмотрела бы все документы XML в поисках соответствующего значения "ResponseRequest" Это снизило бы рабочую производительность, если, конечно, количество данных не слишком мало. Однако, если запрос, который вы планируете создать по таким представлениям, содержит также существенно ограничивающие предикаты, охватывающие индексированные столбцы традиционных типов SQL (в данном примере "i.id" или "i.itemname"), вы можете сократить проблемы со снижением рабочей производительности. DB2 использует реляционные индексы для фильтрации соответствующих критериям записей и снижения их количества, а затем применяет предикаты любых дополнительных запросов к этим предварительным результатам, прежде чем возвратить окончательное результирующее множество.
Объединение данных XML и реляционных данных
Теперь вам, возможно, будет интересно объединение XML- и не-XML -данных (например, реляционных данных традиционных типов SQL). DB2 дает вам возможность сделать это одним предложением SQL/XML. Хотя существуют различные способы формулировки такого объединения, зависящие от схемы базы данных и требований к рабочей нагрузке, мы рассмотрим только один пример. Возможно, вы будете удивлены, если узнаете, что уже обладаете достаточными знаниями по SQL/XML, чтобы выполнить эту работу.
Вспомните, что XML-столбец в таблице "items" содержит элемент "CustomerID". Он может служить объединяющим ключом для содержащего целые значения столбца "id" таблицы "clients". Так, если вы хотите получить отчет, содержащий фамилии и сведения о статусе клиентов, которые оставили комментарии по поводу одного или нескольких ваших продуктов, вам следует объединить значения элемента XML из одной таблицы с целыми значениями SQL из другой. И один из способов решить эту задачу - это воспользоваться функцией XMLExists, как показано в листинге 14:
Листинг 14. Объединение XML- и не-XML-данных
select clients.name, clients.status from items, clients
where xmlexists('$c/Comments/Comment[CustomerID=$p]'
passing items.comments as "c", clients.id as "p")
|
В первой строке определяются столбцы SQL, которые следует включить в результирующее множество запроса и исходные таблицы, которые упоминаются в запросе. Вторая строка включает предложение объединения. Здесь XMLExists определяет, равно ли значение "CustomerID" в объектном источнике значению, взятому из другого объектного источника. Третья строка определяет эти источники: первый - это XML-столбец "comments" таблицы "items", а второй - столбец целых значений "id" таблицы "clients". Таким образом, если клиент оставил комментарий о любом объекте, и информация об этом клиенте есть в таблице "clients", выражение XMLExists будет равно "true", а фамилия клиента и информация о его статусе будут включены в отчет.
Использование выражений "FLWOR" в SQL/XML
Хотя мы уже обсудили несколько функций, SQL/XML предоставляет еще много эффективных средств для создания запросов на выборку XML-данных и объединения этих данных с реляционными данными. Действительно, мы уже видели несколько примеров того, как это работает, а теперь рассмотрим еще несколько применений.
Обе функции - и XMLExists, и XMLQuery - позволяют включать код XQuery в SQL. В предыдущих примерах было показано, как использовать эти функции вместе с простыми выражениями XPath для перехода к интересующему фрагменту документа XML. Теперь рассмотрим простой пример, в котором XQuery встраивается в запросы SQL.
Запросы XQuery могут содержать некоторые или все следующие операторы: "for," "let," "where," "order by" и "return." Вместе они формируют выражения FLWOR (произносится "флауэр"). Программисты SQL могут счесть удобным включение XQuery в списки SELECT для извлечения (или проецирования) фрагментов XML-документов в (на) свои результирующие множества. И хотя это не единственный способ использования функций XMLQuery, это все, что мы хотели охватить в этой статье (а в следующей статье мы более подробно рассмотрим XQuery).
Давайте предположим, что нам нужно получить имена и основные адреса электронной почты клиентов со статусом "Gold". В некоторых отношениях эта задача похожа на одну из тех, что мы рассматривали раньше (см. листинг 9), когда изучали, как проецировать значения элемента XML. В следующем примере вы составите запрос XQuery (с предложениями "for" и "return") в качестве исходных данных для функции XMLQuery
Листинг 15. Получение XML-данных при помощи предложений XQuery "for" и "return"
select name, xmlquery('for $e in $c/Client/email[1] return $e'
passing contactinfo as "c")
from clients
where status = 'Gold'
|
Первая строка определяет, что фамилия клиента и вывод функции XMLQuery будут включены в результирующее множество. Вторая строка показывает, что следует возвратить первый вложенный элемент "email" элемента "Client". Третья строка определяет источник данных XML - столбец "contactinfo". В четвертой строке указано, что этот столбец находится в таблице "clients". И, наконец, пятая строка показывает, что нас интересуют только клиенты, обладающие статусом "Gold".
Из-за того, что этот пример был таким простым, можно было бы написать здесь точно такой же запрос. Но вместо этого вы могли бы написать этот же запрос в более компактном виде, намного более компактном, чем до сих пор:
Листинг 16. Более компактная запись предыдущего запроса
select name, xmlquery('$c/Client/email[1]'
passing contactinfo as "c")
from clients
where status = 'Gold'
|
Однако предложение return из XQuery позволяет при необходимости преобразовать вывод XML. Например, вы можете извлечь значения элемента "email" и опубликовать их в формате HTML. Следующий запрос создаст результирующее множество, в котором первый из адресов электронной почты возвращается в виде параграфа HTML.
Листинг 17. Получение и преобразование данных XML в HTML
select xmlquery('for $e in $c/Client/email[1]/text()
return <p>{$e}</p>'
passing contactinfo as "c")
from clients
where status = 'Gold'
|
Первая строка показывает, что вас интересует текстовое представление первого из адресов электронной почты клиентов, соответствующих критериям запроса. Вторая строка определяет, что эта информация перед возвращением должна быть заключена в тэги HTML "параграф". В частности, фигурные скобки ({}) дают указание DB2 вычислить вложенное выражение (в данном случае, "$e"), а не рассматривать его как строку символов. Если вы пропустите фигурные скобки, DB2 возвратит результирующее множество, содержащее "<p>$e</p>" для каждой записи о клиенте, соответствующем критериям запроса.
Публикация реляционных данных в виде XML
До сих пор мы фокусировали внимание на способах запроса, извлечения или преобразования данных, содержащихся в XML-столбцах DB2. И, как вы могли убедиться, эти средства доступны в любом предложении SQL/XML.
SQL/XML предоставляет также другие полезные функции. Среди них - возможность преобразовать или опубликовать реляционные данные в формате XML. В нашей статье описывается этот аспект для трех функций SQL/XML: XMLElement, XMLAgg, и MLForest.
Функция XMLElement позволяет преобразовывать данные, хранимые в традиционных столбцах SQL, в фрагменты XML. То есть, вы можете строить элементы XML (с атрибутами XML или без них) из ваших основных данных SQL. Следующий пример описывает использование этой возможности функцией XMLElement для создания серии элементов объектов, каждый из которых содержит вложенные элементы для значений идентификатора, названия фирмы и единицы хранения ("sku"), полученные из таблицы "items":
Листинг 18. Использование функции XMLElement для публикации реляционных данных в формате XML
select xmlelement (name "item", xmlelement (name "id", id), xmlelement (name "brand", brandname), xmlelement (name "sku", sku) ) from items where srp < 100 |
Выполнение этого запроса генерирует результаты, представленные в листинге 19:
Листинг 19. Пример вывода возвращаемых данных для предыдущего запроса
<item> <id>4272</id> <brand>Classy</brand> <sku>981140</sku> </item> . . . <item> <id>1193</id> <brand>Natural</brand> <sku>557813</sku> </item> |
Вы можете комбинировать функцию XMLElement с другими функциями для публикации из арсенала SQL/XML, чтобы строить и группировать значения XML, по желанию создавая из них иерархии. Пример из листинга20 использует функцию XMLElement для создания элемента "custometList", содержимое которого группируется по значениям в столбце "status". Для каждой записи в столбце "customerList" функция XMLAgg возвращает последовательность элементов для клиентов, причем каждый элемент содержит вложенные элементы, созданные из столбцов "name" и "status". Более того, как вы видите, значения элемента "customer" упорядочены по фамилии клиента.
Листинг 20. Агрегация и группировка данных
select xmlelement(name "customerList", xmlagg (xmlelement (name "customer", xmlforest (name as "fullName", status as "status") ) order by name ) ) from clients group by status |
Предположим, наша таблица "clients" содержит три неповторяющихся значения "status": "Gold," "Silver," и "Standard." Выполнение предыдущего запроса заставит DB2 возвратить три элемента "customerList", каждый из которых может содержать несколько вложенных элементов "customer", с информацией о фамилии и статусе. Таким образом, вывод будет выглядеть так:
Листинг 21. Пример вывода возвращаемых данных для предыдущего запроса
<customerList>
<customer>
<fullName>Chris Bontempo</fullname>
<status>Gold</status>
</customer>
<customer>
<fullName>Ella Kimpton</fullName>
<status>Gold</status>
</customer>
. . .
</customerList>
<customerList>
<customer>
<fullName>Lisa Hansen</fullName>
<status>Silver</status>
</customer>
. . .
</customerList>
<customerList>
<customer>
<fullName>Rita Gomez</fullName>
<status>Standard</status>
</customer>
. . .
</customerList>
|
Операции удаления и изменения
Хотя в данной статье акцент делается на поиске и получении при помощи запросов SQL данных, хранимых в столбцах XML, имеет смысл уделить время нескольким моментам, позволяющим решить две другие распространенные задачи: изменение и удаление данных в столбцах XML.
DB2 позволяет пользователям изменять и удалять XML-данные при помощи предложений SQL/XML. Действительно, поскольку первоначальный проект спецификации XQuery не решал этих задач, пользователи DB2 должны решать эти задачи с использованием SQL.
Изменение XML-данных
DB2 позволяет изменять столбцы XML при помощи предложения SQL UPDATE или при помощи предоставляемых системой хранимых процедур (DB2XMLFUNCTIONS.XMLUPDATE). В обоих случаях, изменение столбца XML происходит на уровне документа, а не на уровне элемента. Тем не менее, программистам, которые для изменения используют хранимые процедуры, не нужно полностью представлять документ XML в DB2; необходимо только определить элементы XML, которые подлежат изменению, и DB2 сохранит как неизмененные данные документа, так и изменения для выбранных элементов. Программистам, создающим предложения UPDATE, необходимо определить документ полностью (а не только те элементы, которые они хотят изменить).
Например, если вы хотите создать предложение UPDATE для изменения адреса электронной почты из контактной информации конкретного клиента, вам придется предоставить полный набор контактной информации, а не только значение нового элемента "email". Как показано на рисунке 2, этот набор будет включать информацию "Address", "phone", "fax" и "email".
Рассмотрим следующее предложение:
Листинг 22. Простое предложение UPDATE
update clients set contactinfo=( xmlparse(document '<email>newemail@someplace.com</email>' ) ) where id = 3227 |
Если вы помните, как мы выполняли вставку данных XML в статье "Getting off to a start start with DB2 Viper" (Начинаем работать с программой DB2 Viper), то многие из этих предложений должны показаться вам знакомыми. Как и любое предложение SQL UPDATE, данный пример сначала определяет таблицу и столбец, которые должны быть изменены. Поскольку целевой столбец содержит данные XML, вам нужно предоставить в качестве нового объектного значения правильно сформированный документ XML. Хотя большинство производственных систем используют серверные переменные или параметрические маркеры в приложениях для изменения своих XML-данных, здесь показан простой способ сделать это интерактивно. Вторая строка использует функцию XMLParse для преобразования строкового значения в XML. Данная бета-версия программы Viper требует вызова исключительно функции XMLParse, как показано в этом листинге. Ожидается, что в официальной версии Viper такой метод станет необязательным. Заключительная строка - это стандартное предложение SQL, ограничивающее изменение конкретной записью таблицы.
Если вы выполните предыдущее предложение UPDATE, то столбец "contactinfo" для клиента 3227 будет содержать только информацию об адресе электронной почты, как показано в листинге 23:
Листинг 23. Результат выполнения предыдущего предложения UPDATE
<email>newemail@someplace.com</email> |
Адрес, номер телефона и номер факса данного клиента (см. рисунок 2) будут утеряны. Более того, некоторые из созданных ранее запросов, которые вы написали для извлечения адресов электронной почты клиентов, больше не возвратят данный адрес. Почему? Написанные ранее запросы включали выражения XPath или XQuery, которые проходили по определенной иерархии документа, в которой "Client" был корневым элементом, а "email" - вложенным элементом. После продемонстрированного изменения документа элемент "email" стал корневым элементом для данной XML-записи о клиенте, поэтому его значение не будет обнаружено в ожидаемом месте иерархии.
Если вы хотите изменить адрес электронной почты данного клиента интерактивно и сохранить всю прочую имеющуюся контактную информацию, перепишите этот запрос так, как показано в листинге 24:
Листинг 24. Отредактированное предложение UPDATE
update clients set contactinfo= (xmlparse(document '‘<Client> <Address> <street>5401 Julio Ave.</street> <city>San Jose</city> <state>CA</state> <zip>95116</zip> </Address> <phone> <work>4084633000</work> <home>4081111111</home> <cell>4082222222</cell> </phone> <fax>4087776666</fax> <email>newemail@someplace.com</email> </Client>' ) ) where id = 3227 |
Возможно, вам интересно, можно ли избежать определения полного XML-документа, выполняя изменения через представление. Например, представление "comment", определенное в листинге 13 , использует функцию XMLTable для точного извлечения конкретных элементов из столбца XML и преобразования их в столбцы SQL в представлении. Можно ли после этого изменить значение одного из этих столбцов SQL, записав тем самым данный результат в корректный вложенный элемент оригинального XML-документа? Нет. DB2 различает представление столбца на основании типов SQL и представление столбца, которое взято из вывода какой-либо функции (в данном случае, функции XMLTable). Изменения последнего не поддерживаются.
Удаление XML-данных
Удаление записей, содержащихся в столбцах XML, представляет собой прямую процедуру. Предложение SQL DELETE позволяет определить (или ограничить) записи, которые вы хотите удалить, посредством предложения WHERE. Это предложение может включать простые предикаты для идентификации значений не-XML-столбцов или SQL/XML функций для определения значений элементов XML, содержащихся в столбцах XML.
Например, в следующем листинге показано, как можно удалить всю информацию для клиента с ID 3227:
Листинг 25. Удаление данных для определенного клиента
delete from clients where id = 3227 |
Помните, как ограничить предложения SQL SELECT, чтобы добиться возвращения записей только для тех пользователей, которые имеют почтовый индекс 95116? Если да, то вы можете просто применить свои знания для удаления записей, которые оставили такие клиенты. В следующем листинге показано, как выполнить эти действия при помощи функции XMLExists:
Листинг 26. Удаление данных для клиентов, имеющих определенный почтовый индекс
delete from clients
where xmlexists('$c/Client/Address[zip="95116"]'
passing clients.contactinfo as "c");
|
Индексирование
И наконец, ничего не стоит научиться создавать специализированные индексы XML для ускорения доступа к данным, хранимым в столбцах XML. Мы не будем рассматривать эту тему в данной статье из-за вводного характера статьи и недостаточного объема данных для упражнений. Однако, в производственной обстановке определение соответствующих индексов может быть критически важным для достижения оптимальной производительности.
Заключение
В статье были рассмотрены некоторые основы языка, при этом были особо отмечены отдельные ключевые аспекты SQL/XML и принципы их использования для создания и выполнения запросов на выборку данных в столбцах XML. Безусловно, при помощи функций SQL и SQL/XML можно сделать намного больше, чем мы смогли рассмотреть в этой статье. В статью включен простой пример на языке Java, демонстрирующий, как можно использовать маркеры параметров вместе с SQL/XML для запроса данных в столбцах XML. Более подробно мы рассмотрим проблемы разработки приложений в одной из следующих статей. А в следующей статье будут изучаться некоторые интересные аспекты нового языка запросов, поддерживаемого DB2 Viper - XQuery.