DB2 и XML: Запрос XML-данных при помощи SQL

Опции документа Опции документа, требующие включения JavaScript, не отображаются Обсудить Выскажите мнение об этой странице Помогите нам улучшить содержание

Уровень сложности: простой

Синтия M. Саракко (Cynthia M. Saracco), старший инженер-программист, IBM
Дон Чамберлин (Don Chamberlin), научный сотрудник IBM, IBM
Рев Ахуджа, менеджер по международному продвижению DB2, IBM

19.01.2007

В этой главе вы узнаете, как создавать запросы к данным, хранящимся в XML-столбцах при помощи SQL и SQL/XML в DB 2 9 (версии, которая ранее носила кодовое название "Viper")

В этой главе вы узнаете, как создавать запросы к данным, хранящимся в XML-столбцах при помощи SQL и SQL/XML в DB 2 9 (версии, которая ранее носила кодовое название "Viper"). Следующая глава (Глава 5, «Запрос XML-данных при помощи XQuery») расскажет о том, как создавать запросы к XML-данным при помощи XQuery - нового языка, поддерживаемого в DB2 .

Хотя гибридная архитектура DB2 существенно отличается от предыдущих версий, использование новых XML-возможностей не является очень сложным процессом. Если вы знакомы с SQL, вы можете сразу начать применять ваши навыки к работе с XML-данными, которые хранятся в DB2 в своем исходном формате. Как это сделать, вы увидите в этой главе.

Вы, должно быть, удивитесь, узнав, что DB2 также поддерживает "двуязычные" запросы, т.е. запросы, в которых применяются выражения и из SQL, и из XQuery. Какой язык (или их комбинацию) использовать - зависит от нужд приложения и ваших навыков. Объединять элементы из двух языков в одном запросе не так сложно, как вы думаете. Но такая операция дает вам мощные возможности для проведения поиска и интеграции SQL и XML-данных.

4.1 Пример базы данных

В запросах, созданных в этой главе, мы будем обращаться к примерам таблиц, созданных в Главе 3, «Быстрое начало работы с pureXML». Дадим краткий обзор. Таблицы “items” и “clients” определены, как показано в Примере 4-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” показан на Рисунке 4-1, а пример XML-данных, включенных в столбец “clients.contactinfo” показан на Рисунке 4-2. В последующих запросах мы будем обращаться к одному из этих документов, или к обоим сразу.

В начало

4.2 Среда выполнения запросов

Все запросы, используемые в этой главе, предназначены для интерактивного запуска, который вы можете осуществить через процессор командной строки DB2 или через редактор DB2 Command Editor в DB2 Control Center. Снимки экрана и инструкции в этой главе относятся к последнему варианту. (DB2 9 поставляется со средой разработки Developer Workbench, основанной на Eclipse, которая может помочь программистам конструировать запросы в визуальном режиме; однако в этой главе мы не обсуждаем вопросы разработки приложений или среду Developer Workbench).

Чтобы использовать DB2 Command Editor, запустите Control Center и выберите пункт меню Tools (Инструменты) -> Command Editor (Редактор команд). Появится окно, похожее на то, которое показано на Рисунке 4-3. Вводите запросы в верхней панели, нажимайте на зеленую стрелку в верхнем левом углу для их запуска и просматривайте выходные данные в нижней панели или на отдельной закладке «Query results» (Результаты запроса).

В начало

4.3 Запросы, включающие только SQL

Даже если ваши знания SQL ограничены, вы все равно с небольшими усилиями сможете выполнять запросы к XML-данным. Например, в следующем запросе (Пример 4-2) выбирается все содержимое таблицы “clients”, включая XML-данные, хранящиеся в столбце “contactinfo”.

select * from clients

Конечно, вы можете создавать более избирательные SQL-запросы, включающие реляционные операции проекции (projection) и ограничения (restriction). В следующем запросе (Пример 4-3) мы получаем ID, имена и контактную информацию по всем клиентам, имеющим статус «Gold». Обратите внимание, что столбец “contactinfo” содержит XML-данные, а два другие столбца - нет.

select id, name, contactinfo
from clients
where status = 'Gold'

Кроме того, как можно ожидать, вы можете создавать представления (view), основанные на таких запросах, как показано ниже на примере представления “goldview” (Пример 4-4).

create view goldview as
select id, name, contactinfo
from clients
where status = 'Gold'

К сожалению, есть много такого, что нельзя сделать при помощи одного SQL. Обычные инструкции SQL позволят вам получать XML-документы целиком (как вы только что видели), но вы не сможете указывать в запросах предикаты XML и не сможете получать фрагменты XML-документов и конкретные значения их элементов. Иными словами, вы не можете проецировать, ограничивать, соединять, агрегировать и упорядочивать фрагменты XML-документов, используя обычный SQL. Например, нельзя получить только адреса электронной почты клиентов со статусом "Gold" или имена клиентов, живущих в области с почтовым кодом "95116". Для формулировки таких запросов вам нужно будет использовать SQL с XML-расширениями (SQL/XML), XQuery или их комбинацию.

В следующем разделе рассматриваются некоторые базовые особенности SQL/XML. В Главе 5, «Запрос XML-данных при помощи XQuery», вы узнаете, как создавать запросы на языке XQuery, а также, как комбинировать XQuery с SQL.

В начало

4.4 Запросы SQL/XML

Как показывает само название, SQL/XML предназначен для того, чтобы служить мостом между мирами SQL и XML. Он создавался как часть процесса стандартизации SQL, и сейчас включает в себя спецификации для встраивания выражений XQuery и XPath в инструкции SQL. XPath представляет собой язык для навигации в XML-документах для поиска элементов и атрибутов. XQuery включает в себя поддержку XPath.

Важно отметить, что в выражениях XQuery (и XPath) учитывается регистр. Например, запрос XQuery, ссылающийся на XML-элемент "zip" не будет относится к XML-элементам с именами “ZIP” или “Zip”. О чувствительности к регистру программисты часто забывают, поскольку синтаксис SQL-запросов позволяет указывать имя одного и того же столбца как “zip”, “ZIP” или “Zip”.

Система DB2 9 включает более 15 функций SQL/XML, которые позволяют вести поиск данных в XML-документах, преобразовывать реляционные данные в XML, XML - в реляционные данные и выполнять другие полезные задачи. В этой главе мы не станем описывать весь диапазон возможностей SQL/XML. Однако мы рассмотрим несколько распространенных проблем создания запросов, и то, как их можно решить, используя основные функции SQL/XML.

В начало

4.4.1 «Ограничение» выборки результатов на основе значений XML-элементов

SQL-программисты часто пишут запросы, которые ограничивают выборку строк из СУБД определенным условием. Например, SQL-запрос, приведенный в Примере 4-3, ограничивает вывод строк из таблицы “clients” так, чтобы выводилась только информация о клиентах со статусом «Gold». В данном случае статус клиента записывается в столбец SQL-типа VARCHAR. Но что, если нам нужно ограничить поиск условием, применимым к данным в XML-столбце? Функция XMLExists из SQL/XML предлагает способ решения этой задачи.

Функция XMLExists позволяет перейти к элементу в XML-документе и проверить выполнение указанного условия. Если указать эту функцию в составе предложения WHERE, то XMLExists ограничит выборку результатов только теми строками, которые содержат XML-документ с указанным значением XML-элемента (иными словами, теми, где указанное значение будет истинным).

Давайте в примере запроса рассмотрим проблему, которую мы поднимали ранее. Представьте, что вам нужно найти имена всех клиентов, которые живут в области, имеющей указанный zip-код . Как вы, должно быть, помните, таблица “clients” хранит адреса клиентов (включая zip-коды) в столбце XML (см. Рисунок 4-2). Используя функцию XMLExists, вы можете найти в XML-столбце нужный zip-код и получить нужный набор данных. В следующем запросе SQL/XML (Пример 4-5) возвращаются имена клиентов, живущих в области с zip-кодом 95116.

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-код (значение “zip”). В последней строке определяется значение “$c” - это столбец “contactinfo” таблицы “clients”. Таким образом, DB2 проверяет XML-данные, содержащиеся в столбце “contactinfo”, переходит от корневого элемента “Client” к элементу “Address”, а затем к элементу “zip”, после чего определяет, живет ли покупатель в области с нужным zip-кодом. Если это так, то XMLExists возвращает значение «истина», и DB2 возвращает имя клиента, ассоциирующееся с данной строкой.

Распространенной ошибкой является формулировка предиката запроса XMLExists, как показано в Примере 4-6.

select name from clients
where xmlexists('$c/Client/Address/zip="95116" '
passing clients.contactinfo as "c")

Хотя данный запрос будет выполняться успешно, результаты не будут ограничены клиентами, проживающими в области с zip-кодом 95116. (Это объясняется семантикой стандарта, а не особенностями DB2). Чтобы результаты включали в себя клиентов, живущих в области с zip-кодом 95116, нужно использовать синтаксис, показанный ранее, в Примере 4-5.

Вас, вероятно, заинтересует, как можно включить запрос, ограничивающий выборку XML-данных, в приложение. Хотя в этой главе тема разработки приложений подробно не рассматривается, у нас есть пример на Java, в котором используется маркер параметра в инструкции SQL/XML и выборка данных ограничивается информацией о покупателях, живущих в области с данным zip-кодом. Вы можете найти этот пример на следующем Web-сайте:

Запрос XML-данных в среде DB2 при помощи языка SQL: Простой Java-пример

В начало

4.4.2 "Проецирование" значений XML-элементов

Давайте теперь рассмотрим несколько иную ситуацию, в которой вам нужно отобразить (спроецировать) XML-значения в возвращаемый набор данных. Иными словами, нам нужно получить одно или несколько значений элементов из XML-документов. Для этого существует ряд способов. Давайте сначала используем функцию XMLQuery для получения значения одного элемента, а затем используем функцию XMLTable для получения значений нескольких элементов и связывания этих значений со столбцами в результирующем наборе данных SQL.

Рассмотрим решение поставленной ранее проблемы: как создать отчет, в котором выводятся адреса электронной почты покупателей со статусом "Gold". В приведенном в Примере 4-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», имеющих один адрес email, то будет выведен набор данных, состоящий из одного столбца и 500 строк, как показано в Примере 4-8.

1
--------------------------------------------
<email>user5976@anyprovider.com</email>
. . .
<email>someID@yahoo.com</email>

Если у некоторых клиентов есть несколько email-адресов, вы, возможно, захотите дать DB2 указание выводить только основной адрес (т.е. первый адрес, указанный в документе “contactinfo”). Для этого вы можете изменить выражение XPath в первой строке запроса, как показано в Примере 4-9.

select xmlquery('$c/Client/email[1]'
passing contactinfo as "c")
from clients
where status = 'Gold'

Наконец, если у некоторых клиентов email-адреса отсутствуют, вам, возможно, придется написать запрос, исключающий пустые значения из результирующего набора данных. Для этого нужно изменить предыдущий запрос, добавив еще один предикат к предложению WHERE, в котором будет проверяться отсутствие информации по email. Вы уже знакомы с функцией SQL/XML, которая позволяет это сделать - это XMLExists. В Примере 4-10 показано, как можно переписать предыдущий запрос, чтобы отфильтровать строки с клиентами, у которых в контактной информации (хранящейся в XML-формате) отсутствует email-адрес.

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-данных. Как и функции XMLExists и XMLQuery, функция XMLTable заставляет DB2 выполнить навигацию по иерархии XML-документа, чтобы найти интересующие нас данные. Однако XMLTable также включает в себя предложения для связывания отобранных XML-данных со столбцами результирующего набора данных, имеющими SQL-типы данных.

Рассмотрим следующий запрос (Пример 4-11), в котором проецируются столбцы, как из реляционных данных, так и из XML-данных, хранящихся в таблице “items”. (Таблица “items” показана на Рисунке 4-1). Значения идентификатора комментария (commented), идентификатора покупателя (customerID) и сообщения (Message) хранятся в XML-документах, которые находятся в столбце "comments". Названия товаров (itemname) хранятся в SQL -столбце типа VARCHAR.

select t.Comment#, i.itemname, t.CustomerID, t.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 с указанием XML-столбца, содержащего нужные данные (“i.comments”) и пути в XML-документах данного столбца к месту, где находятся нужные элементы (в элементе “Comment”, который является потомком корневого элемента “Comments”). Предложение “columns”, занимающее строки с 3 по 5, указывает конкретные XML-элементы, которые нужно связать с выводимыми SQL-столбцами результирующего набора, указанными в строке 1. Также при этом указываются типы данных, в которые нужно преобразовать значения XML-элементов. В данном примере все XML-данные преобразуются в традиционные типы данных SQL.

На Рисунке 4-4 показаны результаты выполнения этого запроса. Как можно видеть, выходные данные представляют собой простой набор данных SQL. Обратите внимание, что имена столбцов были преобразованы в верхний регистр - нормальное явление в SQL.

При желании вы можете использовать функцию XMLTable для создания результирующих наборов данных, включающих также XML-столбцы. Например, следующая инструкция (Пример 4-12) создает результирующий набор, сходный с предыдущим, за исключением того, что данные элемента “Message” содержатся в XML-столбце, а не в столбце SQL VARCHAR.

select t.Comment#, i.itemname, t.CustomerID, t.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

В начало

4.4.3 Создание реляционных представлений XML-данных

Как можно догадаться, функции SQL/XML можно применять для создания представлений. Это особенно полезно, если вы хотите предложить разработчикам SQL-приложений реляционную модель данных, хранящихся в столбцах XML.

Создание реляционного представления данных в XML-столбце ненамного сложнее проецирования значений XML-элементов. Вы просто пишете инструкцию SQL/XML SELECT, которая включает в себя функцию XMLTable, и используете ее в качестве основы определения представления. В следующем примере (Пример 4-13) представление создается на основе информации в XML- и не-XML-столбцах таблицы “items”. (Оно сходно с запросом, приведенным в Примере 4-11).

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-столбцов при запросах к таким представлениям. Поэтому, если вы проиндексировали элемент ResponseRequested и выполнили SQL-запрос, и ограничили выборку результатов определенным значением в столбце "mustrespond", DB2 будет читать все XML-документы и искать соответствующее значение “ResponseRequested”. Если объем ваших данных не очень маленький, это существенно снизит производительность работы. Однако если запросы, которые вы хотите направлять к таким представлениям, также содержат высокоизбирательные предикаты, включающие индексированные столбцы традиционных SQL-типов (в данном примере - “i.id” или “i.itemname”), вы сможете свести к минимуму потенциальные проблемы производительности. В DB2 реляционные индексы используются для фильтрации небольшого числа отобранных строк, а затем дополнительные предикаты XML-запросов применяются к этим временным результатам перед созданием окончательного результирующего набора.

В начало

4.4.4 Соединение XML-данных и реляционных данных

К данному моменту вам, вероятно, стало интересно, как можно соединять XML-данные с не-XML-данными (например, с реляционными данными, основанными на традиционных SQL-типах). DB2 позволяет сделать это при помощи одной инструкции SQL/XML. Хотя существуют разные способы составления таких соединений, зависящих от схемы базы данных и требований по рабочей нагрузке, мы рассмотрим здесь один пример. И вас, вероятно, удивит, что вы уже обладаете всей необходимой информацией для этого.

Вспомните, что XML-столбец таблицы “items” содержит элемент “CustomerID”. Вы можете использовать его как ключ соединения для целочисленного столбца “id” таблицы “clients”. Таким образом, если вам нужно вывести имена и статус покупателей, которые писали комментарии к одному или нескольким вашим продуктам, вам следует соединить значения XML-элементов из одной таблицы с целочисленными SQL-значениями из другой таблицы. Один из способов решения этой задачи - использование функции XMLExists, как показано в Примере 4-14.

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” будет истинным, и имя клиента и информация о его статусе будет включена в отчет.

В начало

4.4.5 Использование выражений “FLWOR” в SQL/XML

Хотя мы обсудили только несколько функций, SQL/XML предлагает много мощных возможностей для создания запросов к XML-данным и для интеграции этих данных с реляционными данными. В действительности вы уже видели несколько примеров, но мы обсудим здесь еще несколько.

Функции XMLExists и XMLQuery позволяют включать выражения XQuery в XML. В наших предыдущих примерах мы показали, как использовать эти функции с простыми выражениями XPath для выхода на интересующую нас часть XML-документа. Теперь давайте рассмотрим простой пример использования XQuery в SQL-запросах.

Запросы XQuery могут включать в себя некоторые или все следующие предложения: for, let, order by и return. В совокупности их называют FLWOR-выражениями (произносится как «флауэр»). Программисты SQL могут счесть их удобными для включения запросов XQuery в свои выражения SELECT для извлечения (или проецирования) фрагментов XML-документов в результирующие наборы данных. И хотя это не единственный способ использования функции XMLQuery, в этой главе мы рассмотрим этот сценарий. (В Главе 5, «Запрос XML-данных при помощи XQuery», мы рассмотрим язык XQuery более подробно).

Давайте представим, что нам нужно получить имена и основные email-адреса клиентов со статусом «Gold». В некотором отношении эта задача сходна с той, которую мы решали ранее (Пример 4-9) при изучении проецирования значений XML-элементов. Здесь мы передаем выражение XQuery (с предложениями for и return) в качестве входной информации для функции XMLQuery. См. Пример 4-15.

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”. В строке 4 указывается, что столбец находится в таблице “clients”. Наконец, пятая строка указывает, что нас интересуют только клиенты со статусом “Gold”.

Поскольку этот пример очень простой, вы вполне могли бы написать его. Однако тот же запрос можно написать в более компактном виде, примерно так же, как это делалось выше. См. Пример 4-16.

select name, xmlquery('$c/Client/email[1]'
passing contactinfo as "c")
from clients
where status = 'Gold'

Однако предложение return в XQuery позволяет вам преобразовывать выходные данные XML в соответствии с необходимостью. Например, вы можете извлекать значения элементов email и публиковать их в виде HTML. В следующем запросе (Пример 4-17) мы создаем результирующий набор данных, в котором первый email-адрес каждого из покупателей со статусом Gold возвращается в форме абзаца HTML.

select xmlquery('for $e in $c/Client/email[1]/text()
return <p>{$e}</p>'
passing contactinfo as "c")
from clients
where status = 'Gold'

Первая строка показывает, что нас интересует текстовое представление первого email-адреса клиентов, имеющих статус «Gold». Во второй строке указывается, что эта информация перед выводом должна быть заключена в теги абзацев HTML.В частности, фигурные скобки ( {} ) заставляют DB2 вычислять вложенное в них выражение (в данном случае, “$e”), а не рассматривать его как строку-литерал. Если вы пропустите фигурные скобки, то DB2 вернет результирующий набор, содержащий выражение “<p>$e</p>” в каждой записи о покупателе, имеющем статус «Gold».

В начало

4.4.6 Публикация реляционных данных в форме XML

До данного момента основное внимание мы уделяли запросам, извлечению и преобразованию данных, хранящихся в XML-столбце DB2. И, как вы видели, все эти возможности доступны при помощи SQL/XML.

SQL/XML имеет и другие удобные свойства. Среди них можно назвать возможность преобразовывать и публиковать реляционные данные в форме XML. В этой главе мы рассмотрим только три функции, имеющие к этому отношение - XMLElement, XMLAgg и XMLForest.

Функция XMLElement позволяет преобразовывать данные, хранящиеся в традиционных SQL-столбцах во фрагменты XML. То есть, вы можете конструировать XML-элементы (с XML-атрибутами и без них) на основе SQL-данных. В следующем примере (Пример 4-18) функция XMLElement применяется для создания набора элементов «item», каждый из которых содержит подэлементы - идентификатор (id), название товара (brandname) и единицы хранения (sku), полученные из таблицы “items”.

select xmlelement (name "item",
    xmlelement (name "id", id),
    xmlelement (name "brand", brandname),
    xmlelement (name "sku", sku) ) from items
where srp < 100

Выполнение этого запроса даст результат, похожий на то, что приведено в Примере 4-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-значений и формирования нужных иерархий. В Примере 4-20 XMLElement используется для создания элементов customerList, содержимое которых сгруппировано по значению в столбце “status”. Для каждой записи customerList функция XMLAgg возвращает последовательность элементов customer, каждый из которых включает набор подэлементов, основанных на значениях столбцов “name” и “status”. Более того, можно видеть, что значения элементов customer отсортированы по имени покупателя (name).

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.” Таким образом, выходные данные будут сходны с теми, которые приведены в Примере 4-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>

Хотя основное внимание в этой главе мы уделяем поиску и извлечению данных, хранящихся в XML-столбцах при помощи SQL, стоит потратить немного времени на рассмотрение двух других обычных задач: обновление и удаление данных в XML-столбцах.

DB2 позволяет пользователям обновлять и удалять XML-данные, используя инструкции SQL и SQL/XML. Фактически, из-за того, что первые версии стандарта XQuery не решают этих проблем, пользователи DB2 должны при решении этих задач применять SQL.

В начало

4.5.1 Обновление XML-данных

DB2 позволяет обновить XML-столбец при помощи SQL-инструкции UPDATE или при помощи системной хранимой процедуры (DB2XMLFUNCTIONS.XMLUPDATE). В обоих случаях обновление XML-столбца производится на уровне документа, а не на уровне элемента. Однако программисты, которые проводят обновление, используя хранимую процедуру, не должны предоставлять системе полный XML-документ. Они просто должны указать обновляемые XML-элементы, и DB2 сохранит неизменяемые элементы и изменит указанные элементы. Программисты, выполняющие инструкцию UPDATE, должны предоставить весь документ (а не только изменяемые элементы).

Например, если вы хотите выполнить инструкцию UPDATE для изменения email-адреса в контактной информации конкретного клиента, вам нужно предоставить полный набор контактной информации, включаемой в XML-столбец, а не только значение элемента email. Если обратиться к Рисунку 4-2, то сюда относятся элементы “Address”, “phone” и “email”.

Рассмотрим следующую инструкцию, приведенную в Примере 4-22.

update clients set contactinfo=(
xmlparse(document '<email>newemail@someplace.com</email>' ) )
where id = 3227

Если вы вспомните, как мы вставляли XML-данные в Главе 3, «Быстрое начало работы с pureXML», то большая часть этой инструкции выглядит сходным образом. Как и в любой SQL-инструкции UPDATE, в этом примере сначала определяется таблица и столбец, которые нужно обновить. Поскольку целевой столбец содержит XML-данные, вам нужно указать в качестве нового значения правильно сформированный XML-документ. Хотя в большинстве рабочих систем для обновления XML-данных в приложениях используются хост-переменные или маркеры параметров, я продемонстрировал простой способ интерактивного выполнения данной операции. Во второй строке используется функция XMLParse, которая преобразует входную строку в XML. Явный вызов функции XMLParse, показанный здесь, является дополнительной опцией DB2 9. В последней строке используется стандартное предложение SQL, ограничивающее обновление конкретной строкой таблицы.

Если вы выполните предыдущую инструкцию UPDATE, то столбец "contactinfo" клиента 3227 будет содержать только информацию об адресе электронной почты, как это показано в Примере 4-23.

<email>newemail@someplace.com</email>

Адрес, номера телефонов и номер факса для данного клиента (показанные на Рисунке 4-2) окажутся потерянными. Более того, некоторые ранее написанные вами запросы по извлечению email-адресов клиентов не будут извлекать этот адрес. Почему? В ранее написанных запросах есть выражения XPath или XQuery, которые производят навигацию по конкретной иерархии документа, в которой элемент Client является корневым элементом, а элемент email является его элементом-потомком. После обновления документа так, как это было показано, элемент email теперь будет корневым элементом XML-записи о данном клиенте. Следовательно, это значение не будет обнаруживаться в конкретном месте иерархии.

Если вы хотите обновить email-адрес данного клиента интерактивным образом и сохранить всю прочую уже существующую контактную информацию, перепишите свой запрос так, как показано в Примере 4-24.

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-документа путем проведения обновления через представление. Например, в представлении commentview, созданном в Примере 4-13, для извлечения некоторых элементов XML-столбца и преобразования их в столбцы представления используется функция XMLTable. Можно ли обновить значение одного из этих SQL-столбцов и сделать так, чтобы результат был занесен в соответствующий элемент исходного XML-документа? Нет. В DB2 различаются столбцы представлений SQL-типов и столбцы представлений, полученных по выходным данным функции (в данном случае, функции XMLTable). Обновления в последнем случае не поддерживаются.

В начало

4.5.2 Удаление XML-данных

Удаление строк, содержащих XML-столбцы, - процесс достаточно простой. Инструкция SQL DELETE позволяет указать (или ограничить) строки, которые нужно удалить при помощи предложения WHERE. Это предложение может включать в себя простые предикаты, идентифицирующие значения в не-XML-столбцах, или функции SQL/XML, идентифицирующие значения XML-элементов, содержащихся в XML-столбцах.

Например, ниже показано, как можно удалить всю информацию о покупателе с идентификатором 3227 (Пример 4-25).

delete from clients 
where id = 3227

Вы помните, как задавались ограничения в SQL-инструкции SELECT, чтобы она возвращала только строки о покупателях, проживающих в области с zip-кодом 95116? Если да, то вы легко можете применить эти знания для удаления информации об этих покупателях. Вот как это делается при помощи функции XMLExists (Пример 4-26).

delete from clients
where xmlexists('$c/Client/Address[zip="95116"]'
passing clients.contactinfo as "c");

В начало

4.5.3 Индексирование

Наконец, стоит отметить, что вы можете создавать специализированные XML-индексы для ускорения доступа к данным, хранящимся в XML-столбцах. Поскольку данная глава - вводная, и примеры небольшие, мы не будем здесь рассматривать данную тему. Однако в рабочих системах правильное создание индексов может быть критичным для получения оптимальной производительности. В разделе «Resources» приведенного ниже Web-сайта вы можете получить дополнительную информацию о новой технологии индексирования DB2:

Запрос XML-данных в среде DB2 при помощи языка SQL

В начало

4.6 Заключение

В этой главе дается масса базовой информации, охватывающей ключевые аспекты SQL/XML и использование этого языка для составления запросов к данным в XML-столбцах. Конечно, с помощью SQL и SQL/XML вы можете сделать гораздо больше, чем мы описали здесь. В этой главе есть простой пример на Java, иллюстрирующий то, как можно использовать маркеры параметров в SQL/XML для написания запросов к данным в XML-столбцах. Обращайтесь к следующему Web-сайту:

Запрос XML-данных в среде DB2 при помощи языка SQL: Простой Java-пример

Мы обсудим вопросы разработки приложений более детально в Главе 6, «Разработка Java-приложений для работы с данными в формате XML». Однако в следующей главе мы изучим некоторые интересные аспекты XQuery, нового языка запросов, поддерживаемого DB2 9.

Об авторах

		C. M. Саракко (Cynthia M. Saracco) работает в исследовательской лаборатории компании IBM в Силиконовой долине, в объединении DB2 XML. Сфера ее служебных интересов – управление базами данных, XML, разработка веб-приложений и смежные темы.

		Дон Чамберлин, научный сотрудник исследовательского центра IBM Almaden Research Center; является одним из представителей IBM в рабочей группе по XML концерна W3C и одним из авторов проекта языка Quilt, который сформировал основу для разработки языка XQuery. Наибольшую известность он приобрел как соавтор языка базы данных SQL и автор двух книг по системе баз данных DB2. Дон имеет ученые степени бакалавра наук от колледжа Harvey Mudd College и доктора философии Стенфордского университета. Он также является научным сотрудником Ассоциации по вычислительной технике (ACM) и членом Национальной инженерной академии США.

		Рев Ахуджа (Rav Ahuja) - менеджер по международному продвижению DB2, работающий в IBM Toronto Lab. Он работает с DB2 для Linux, Unix и Windows начиная с самой первой версии. Он выполнял самые разные функции, связанные с разработкой DB2, технической поддержкой, маркетингом и выработкой стратегии для продукта. Он работает с клиентами и партнерами по всему миру, помогая им создавать и получать преимущества от использования DB2 и решений на основе служб. Рев часто принимает участие в создании документов, статей и книг, посвященных DB2. Он имеет степень по вычислительной технике, полученную в McGill University, а также степень MBA, полученную в University of Western Ontario.

Выскажите мнение об этой странице

Пожалуйста, найдите минутку и заполните форму, чтобы повысить уровень сервиса. Помогла ли данная информация достичь Вашей цели? Да Нет Не знаю   Пожалуйста, напишите комментарий, чтобы мы могли улучшить данную страницу:   Насколько полезна эта информация? (1 = Бесполезна, 5 = Очень полезна) 1 2 3 4 5

Поделиться этой статьей: забобрить сохранить в memori

В начало