サンプルXMLデータベース ~ XQueryを試してみよう ~ IBM Software Group | DB2 Information Management Software 2008/9/23

IBM Software Group | DB2 Information Management Software
サンプルXMLデータベース
~ XQueryを試してみよう ~
2008/9/23
© 2008 IBM Corporation
IBM Software Group | DB2 Information Management Software
はじめに
DB2® 9以降では、“XMLデータの格納、管理、検索”サポートが１つの大
きな特長です。本資料は、XMLサンプル・データを使ったXMLデータの初
歩的な検索についてご紹介します。
本資料でご紹介した内容は、バージョンアップなどにより予告なしに変更
されてることがあります。
記載内容について万一お気づきの点がございましたら、ご一報いただけ
れば幸甚です
2
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
サンプル・データのシナリオ
2008/9/23
© 2008 IBM Corporation
IBM Software Group | DB2 Information Management Software
シナリオ ： リレーショナル・データベース (1)
業界最大手でシェアNo.1を誇るセレクト・ショップOでは、商品のデータを
リレーショナル・データベースで管理しています
このショップでは扱っている商品が洋服はもちろん、その他に小物やグッ
ズまで多岐に渡っていて、商品の属性まで含めて正規化すると属性の分
だけ表が増えてしまうので、正規化は行っていません
属性はサイズと色だけを商品テーブルで保管しています。現在はサイズ
のカラムは3つ、色のカラムは4つとなっています
4
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
シナリオ ：リレーショナル・データベース (2)
表の定義文
CREATE TABLE item(
id
INTEGER NOT NULL PRIMARY KEY,
商品 VARCHAR(50),
価格 INTEGER,
サイズ1 CHAR(1),
サイズ2 CHAR(1),
サイズ3 CHAR(1),
色1 VARCHAR(10),
色2 VARCHAR(10),
色3 VARCHAR(10),
色4 VARCHAR(10)
);
5
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
シナリオ ： XMLデータベース (1)
業界No.2のシェアを持つセレクト・ショップDでは、商品のデータをXML
データベースで管理しています
このショップでは扱っている商品が洋服はもちろん、その他に小物やグッ
ズまで多岐に渡っていますが、商品の属性はXMLで保管しています
XML形式で属性を管理しているため、新商品で新しい属性（素材、生産
地など）が増えても、表の構造を変えることなく、全ての属性を保管するこ
とが出来ます
6
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
シナリオ ： XMLデータベース (2)
表の定義文とXMLデータの例
CREATE TABLE xmlitem(
id
INTEGER NOT NULL PRIMARY KEY,
item XML
);
<アイテム>
<商品 id="10101">
Tシャツ（長袖）
</商品>
<価格>
1000
</価格>
<サイズ>S</サイズ><サイズ>M</サイズ><サイズ>L</サイズ>
<色>黒</色><色>赤</色><色>青</色>
<素材>綿</素材>
</アイテム>
7
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
XMLデータベースの柔軟性(1)
セレクト・ショップOとDは、同時期に事業を拡大しシルバー・アクセサリー
分野に進出することを決定しました
指輪などはサイズ属性が、洋服のようなS、M、L、XL…といったものでは
なく、1号、2号、3号…というような形式になっています
また、ペンダントなどはサイズ属性はチェーンの長さ（40cm、50cm、
60cm…）になります
今回扱うアクセサリーはサイズ属性の形式が変わり、種類も増えるため
に、ECサイトの商品検索の仕組みなど、システムの変更が必要になりま
す
8
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
XMLデータベースの柔軟性(2)
サイズで絞って商品検索を行う画面の変更例
セレクト・ショップO
1. サイズ属性が3つまでしか持て
ないため、テーブルにカラムを追
加
属性はXML形式で持っているため
テーブル構造の変更の必要はない
2. SQL文の変更
テーブル構造に変更がないため、
SQLの変更も必要はない
3. 取り出すカラムが増えるので、
データの取り出し部分の変更
結果はXMLカラムで取り出すため、
この部分の変更の必要はない
4. HTML表示部分の変更（JSP）
9
セレクト･ショップD
DB2
HTML表示部分の変更（XSLT）
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
XMLデータベースの柔軟性(3)
セレクト・ショップDは、XMLデータベースを使って柔軟な商品データの管
理を行っていたために、システムの変更を非常に短期間で終えることが
出来た
一方、セレクト・ショップOは、リレーショナル・データベースを使っていた
ために、システムの変更に多くの時間とコストを費やした
この結果、セレクト・ショップOのシルバー・アクセサリー事業は、セレクト・
ショップDに遅れを取ってしまい、業界でのシェアもセレクト・ショップDに
逆転を許すこととなってしまった
10
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
ファイルの説明
サンプル・データには以下のファイルが含まれます
11
– create_table.ddl
XML列を含む表の定義文
– load.ddl
XMLを含むデータのロード文
– load_xml.txt
区切りASCIIファイル
– xml/*.xml
XMLデータ
– xquery/*.sql
Xquery文
– create_table_current.ddl
リレーショナル表
– load_current.ddl
リレーショナル・データのロード文
– item.txt
区切りASCIIファイル
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
XMLデータベース・XMLテーブルの作成
2008/9/23
© 2008 IBM Corporation
IBM Software Group | DB2 Information Management Software
0. 環境の準備
この資料ではDB2 9.5とIBM Data Studioを使って説明していますので、以下
のURLを参考に環境を準備して下さい
DB2 Express-C 9.5 for Windows クイックインストール
– http://www.ibm.com/developerworks/jp/offers/db2express-c/installwin_v95/
DB2 Express-C 9.5 for Linux クイックインストール
– http://www.ibm.com/developerworks/jp/offers/db2express-c/installlin_v95/
IBM Data Studioクイック・スタート
– http://www.ibm.com/developerworks/jp/xml/library/j_x-datastudio/
XPathやXQueryの実行環境を準備する
– http://www.ibm.com/developerworks/jp/xml/library/j_x-xmlmaster/
13
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
1. データベースの作成
XMLサンプル・データベースの作成
– Unicodeデータベースを作成します
– DB2 Viperにおいて、XMLドキュメント および 従来形式のSQLデータ
両方を格納できるのはUnicode形式のデータベースです。
– Unicodeデータベース作成後は、特別なコマンドを発行したり、さらに操
作を行う必要はありません。(ネイティブの階層形式でDB2にXMLデー
タを格納できます)
定義文
– create database [データベース名] using codeset UTF-8 territory JP
例）
$ db2 create database xmlsampl using codeset UTF-8 territory JP
DB20000I CREATE DATABASE コマンドが正常に完了しました。
14
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
2. XMLテーブルの作成(1)
サンプル・テーブルの作成
– XMLデータを格納するには、1つ以上のXML列が含まれたテーブルを作成
します
– これらのテーブルはドキュメント・コレクションの論理コンテナとして機能
します
– DB2の内部では、実際にはXMLデータと非XMLデータに対して異なる
格納スキームを使用しています。様々な形式のデータを管理する１つ
の論理オブジェクトとしてテーブルを使用する事で、管理とアプリケー
ション開発をシンプルにできます
15
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
2. XMLテーブルの作成(2)
定義文
CREATE TABLE xmlitem(
id
item
INTEGER NOT NULL PRIMARY KEY,
XML
);
例）
$ db2 connect to xmlsampl
$ db2 -tvf create_table.ddl
CREATE TABLE xmlitem( id
INTEGER NOT NULL PRIMARY KEY, item
XML )
DB20000I SQL コマンドが正常に完了しました。
16
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
3. XMLデータのロード(1)
LOADユーティリティーの使用
– XMLデータがファイルになっている場合、DB2のLOADユーティリティーが使
用できます
– アプリケーションの作成は不要です
– LOADに必要なもの
– XMLドキュメントのファイル
– 区切りASCIIファイル
– 任意のテキスト・エディターを使って作成します(一般に拡張子
は.delとします)
– ファイル内の各行は、テーブルにロードされる1行のデータです
– 行にXMLデータ指定子(XDS)が含まれている場合、LOADは参
照先XMLファイルに含まれているデータを読み取り、それをDB2
にロードします
17
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
3. XMLデータのロード(2)
入力用の区切りASCIIファイルの例
18
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
3. XMLデータのロード(3)
ロード文
– load from load_xml.txt of del XML from xml insert into
xmlitem;
例）
$ db2 -tvf load.ddl
load from load_xml.txt of del XML from xml insert into xmlitem
∼ 中略 ∼
19
読み込まれた行数
= 358
スキップされた行数
=0
ロードされた行数
= 358
拒否された行数
=0
削除された行数
=0
コミットされた行数
= 358
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
4. XMLデータの確認(1)
IBM Data Studioを起動して、XMLSAMPLデータベースを開きます
20
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
4. XMLデータの確認(2)
データベース・エクスプローラーのテーブルからXMLITEMを選択し、右
クリックで[データ] -> [サンプルコンテンツ]をクリックします
XML列のボタンをクリックしてXML文書を開いて確認します
21
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
4. XMLデータの確認(3)
[設計] と [ソース] のタブを切り替えてXML文書を確認する事が出来ます
22
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
XMLデータの簡単な検索（XQuery、SQL/XML）
2008/9/23
© 2008 IBM Corporation
IBM Software Group | DB2 Information Management Software
XQueryを使ったXMLデータの検索
db2-fn:xmlcolumn
– XML型のカラムを指定して、XMLデータを取り出します
– XPathで条件を指定して結果を絞り込むことが可能です
db2-fn:sqlquery
– SQLを使い、XMLデータを取り出します
– 条件指定をして結果を絞り込むことが可能です
24
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
XMLデータの検索[XQuery] (1)
XQUERYとdb2-fn:xmlcolumnを使い、全てのXMLデータを検索
XQUERY db2-fn:xmlcolumn("XMLITEM.ITEM") %
25
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
XMLデータの検索[XQuery] (2)
XQUERYとdb2-fn:sqlquery を使い、全てのXMLデータを検索
XQUERY db2-fn:sqlquery("select item from xmlitem") %
26
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
XMLデータの検索[XQuery] (3)
XQUERYとdb2-fn:xmlcolumnを使い、サイズがLの商品を検索
XQUERY db2-fn:xmlcolumn('XMLITEM.ITEM')/アイテム[サイズ='L']/商品 %
27
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
XMLデータの検索[XQuery] (4)
XMLデータの検索(3)を参照
XQUERY db2-fn:xmlcolumn('XMLITEM.ITEM')/アイテム[サイズ='L' and 色='黒']/商品 %
28
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
XMLデータの検索[XQuery] (5)
XMLデータの検索(3)を参照、サイズが120cmで青色の商品を検索
XQUERY db2-fn:xmlcolumn('XMLITEM.ITEM')/アイテム[サイズ='120cm' and 色='青']/商品 %
29
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
XMLデータの検索[XQuery] (6)
XMLデータの検索(3)を参照、サイズが120cmで青色、1,000円以下の商品を検索
XQUERY db2-fn:xmlcolumn('XMLITEM.ITEM')/アイテム[サイズ='120cm' and 色='青' and 価格 <= 1000]/商品 %
30
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
XMLデータの検索[XQuery] (7)
XQUERYとdb2-fn:xmlcolumnを使い、idが10101である商品を検索
XQUERY db2-fn:xmlcolumn('XMLITEM.ITEM')//商品[@id="10101"] %
31
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
XMLデータの検索[XQuery] (8)
XQUERYとdb2-fn:xmlcolumn、countを使い、サイズがLの商品点数を数える
XQUERY count(db2-fn:xmlcolumn('XMLITEM.ITEM')/アイテム[サイズ='L']/商品) %
32
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
SQL/XMLを使ったXMLデータの検索
xmlexists
– xmlexists述部は、条件を指定してXMLカラムの絞り込むことが出来ます
xmlquery
– xmlqueryは、SQLからXQueryを実行するためのSQLスカラ関数です
33
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
XMLデータの検索[SQL/XML] (9)
SQLとxmlexistsを使った検索の例、商品名が“フランス風カーディガン（七分袖）”であ
る商品を検索
select c.id, c.item from xmlitem c where
xmlexists('$i/アイテム[商品 = "フランス風カーディガン（七分袖）"]'
passing c.item as "i") %
34
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
XMLデータの検索[SQL/XML] (10)
SQLとxmlqueryを使った検索の例、商品コードが’20401’の商品を検索
select xmlquery('$i/アイテム/商品 ' passing c.item as "i") from xmlitem c
where c.id = 20401 %
35
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
【参考】 db2 コマンドライン・オプション
db2コマンドライン・オプション
– SQL文の区切り文字を指定
– オプション ： -td’%’
– XQueryでは、セミコロン（;）をSQL文中で使用することもあるので、セミ
コロン（;）以外の区切り文字が必要
– XMLデータにインデントを付けて表示
– オプション ： -I
– XMLデータのXML宣言を表示
– オプション ： -d
使用例）
– db2 -td'%' -ivf query_01.sql
36
DB2
© 2006 IBM Corporation
IBM Software Group | DB2 Information Management Software
END
37
DB2
© 2006 IBM Corporation