Запит XML-даних в середовищі DB2 за допомогою мови SQL (исходники) Інтеграція додатків і даних, Бази даних, статті

Хоча гібридна архітектура 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:


Лістинг 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”

При бажанні ви можете використовувати функцію XMLTable для створення результуючого безлічі, яке включає і стовпці XML. Наприклад, наступна пропозиція генерує таке ж результуюче безліч, як і в попередньому прикладі, за винятком того, що дані про “Повідомленнях” міститися в стовпці XML, а не в стовпці SQL VARCHAR.


Лістинг 12. Отримання кількох елементів XML та приведення кожного до традиційного типу даних SQL або XML





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 поверне результуюче безліч, що містить “

$ e ” для кожного запису про клієнта, відповідному критеріям запиту.


Публікація реляційних даних у вигляді 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.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*